In Excel VBA I couldn't change all occurrences between {MyText} to lowercase.

I have been browsing the whole column A & B and each time I found a text between {} I change it to lowercase.

The code I had works only on the first occurrence but when there are multiple occurrences in the cell I would not be able to change all of them to lowercase.

as e.g. in a cell we had "Bla Bla Bla {Abc} bla bla {xYz} and {HELLO}"

I would have as a result "Bla Bla Bla {abc} bla bla {xYz} and {HELLO}"

However I am expecting to have it like this:

"Bla Bla Bla {abc} bla bla {xyz} and {hello}"

Even if I run the code again it is applying only on the first occurrence this below is the VBA I had:

Set MyRange = Worksheets("Sheet1").Range("G:G,H:H") 
Dim temp As String
For Each c In MyRange 
    If c.Value Like "*{*" Then      
    temp = Split(c.Value, "{")(1)        
    temp = Split(temp, "}")(0)        
    c.Value = Replace(c.Value, temp, LCase(temp))           
    Else
    End If
Next c
End Sub

CodePudding user response：

Regex is a good way to go. However, if you are looking for a VBA solution give the following a try. The code accounts for bad input as illustrated by the test cases:

Option Explicit

Private Sub Test()
   Debug.Print Lowercase("Bla Bla Bla {Abc} bla bla {xYz} and {HELLO}")
   Debug.Print Lowercase("Bla Bla Bla bla bla ")
   Debug.Print Lowercase("Bla Bla Bla and {HELLO")
End Sub

Private Function Lowercase(ByVal Value As String) As String
   Dim i As Integer
   Dim j As Integer
   
   j = 1
   
   Do
      If j > 0 Then
         i = InStr(j, Value, "{")
         
         If i > 0 Then
            j = InStr(i, Value, "}")
            If j > 0 Then Mid(Value, i   1, j - i - 1) = LCase(Mid(Value, i   1, j - i - 1))
         End If
      End If
   Loop While i > 0 And j > 0
   
   Lowercase = Value
End Function

CodePudding user response：

My version of the answer parses the line, because you could have the same text to replace but NOT enclosed by "{}":

Option Explicit

Sub test()
    Dim origText As String
    Dim lowerText As String
    origText = "Bla Abc Bla {Abc} bla bla {xYz} and {HELLO}"
    lowerText = MakeItLower(origText)
    Debug.Print "original text: " & origText
    Debug.Print "   lower text: " & lowerText
End Sub

Function MakeItLower(ByVal text As String) As String
    Dim result As String
    Dim pos0 As Long
    Dim pos1 As Long
    Dim pos2 As Long
    pos0 = 1
    pos1 = InStr(1, text, "{", vbTextCompare)
    Do While pos1 > 0
        result = result & Mid$(text, pos0, pos1 - pos0   1)
        pos2 = InStr(pos1, text, "}", vbTextCompare)
        If pos2 > 0 Then
            Dim textToReplace As String
            textToReplace = Mid$(text, pos1   1, pos2 - pos1 - 1)
            result = result & LCase(textToReplace) & "}"
            pos0 = pos2   1
        End If
        pos1 = InStr(pos2, text, "{", vbTextCompare)
    Loop
    MakeItLower = result
End Function

CodePudding user response：

An easy non-RegEx solution could work like this:

Sub test()
    Debug.Print LowerCaseBrackets("Bla Bla Bla {Abc} bla bla {xYz} and {HELLO}")
End Sub

Public Function LowerCaseBrackets(ByVal InputString As String) As String
    Dim SplitStarts() As String
    
    SplitStarts = Split(InputString, "{")
    
    Dim iStart As Variant
    For iStart = LBound(SplitStarts)   1 To UBound(SplitStarts)
        Dim EndPos As Long
        EndPos = InStr(SplitStarts(iStart), "}")
        SplitStarts(iStart) = LCase(Left$(SplitStarts(iStart), EndPos - 1)) & Mid$(SplitStarts(iStart), EndPos)
    Next iStart
    
    LowerCaseBrackets = Join(SplitStarts, "{")
End Function

The Split will break the string into the following parts:

Bla Bla Bla 
Abc} bla bla 
xYz} and 
HELLO}

Since the first part is always outside the first opening bracket we omit it and start in the following one

For iStart = LBound(SplitStarts)   1

We loop through the following parts of the string and look for the position of the end bracket in this part

EndPos = InStr(SplitStarts(iStart), "}")

Then we take the left art of it convert it to lower case

LCase(Left$(SplitStarts(iStart), EndPos - 1))

and append the rest with

& Mid$(SplitStarts(iStart), EndPos)

After that loop our SplitStarts array looks like:

Bla Bla Bla 
abc} bla bla 
xyz} and 
hello}

And we join it using the opening bracket

LowerCaseBrackets = Join(SplitStarts, "{")

to get our final string

Bla Bla Bla {abc} bla bla {xyz} and {hello}

---

Another alternative solution could be

Public Function LowerCaseBrackets(ByVal InputString As String) As String
    Dim Pos As Long
    Do While InStr(Pos   1, InputString, "{")
        Dim EndPos As Long
        EndPos = InStr(Pos   1, InputString, "}")
        
        Mid(InputString, Pos   1, EndPos - 1) = LCase(Mid(InputString, Pos   1, EndPos - 1))
        Pos = EndPos
    Loop
    LowerCaseBrackets = InputString
End Function

CodePudding user response：

What about this approach?
I propose you to keep track of being inside or outside a curly-brackets "region", using a boolean, something like (±pseudocode):

dim b_inside as Boolean;
dim Line as string; ' this is the text you're going to manipulate

for (int i = 0, i < Line.Length, i  ):
    if Line[i] == "{" then b_inside = True;
    if Line[i] == "}" then b_inside = False;
    
    if b_inside and 
      (Line[i] >= "A") and 
      (Line[i] <= "Z")
    then Line[i] = LowerCase(Line[i])
next i