In Excel with VB I couldn't change all occurrences between {MyText} to lowercase

Question

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

Hi BigBen, Unfortunately, I haven't worked with before would you please help throughout this? Thank you. — SeifeddineBMZ, Mar 22 '22 at 14:08
https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — BigBen, Mar 22 '22 at 14:09
Example for a RegEx pattern you could use: [https://regex101.com/r/1gP1Ms/1](https://regex101.com/r/1gP1Ms/1) — Pᴇʜ, Mar 22 '22 at 14:12

Pᴇʜ · Answer 1 · 2022-03-22T15:37:46.003

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

score 2 · Accepted Answer · answered Mar 22 '22 at 14:54

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

Wow, I'm actually extremely surprised that `Mid(Value, i + 1, j - i - 1) = LCase(Mid(Value, i + 1, j - i - 1))` works. I would never have expected that this will replace anything in `Value` at all. — Pᴇʜ, Mar 22 '22 at 15:01
Perhaps a little surprising but it is in the [official documentation](https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/mid-statement). — Brian M Stafford, Mar 22 '22 at 15:22

score 2 · Answer 3 · answered Mar 22 '22 at 15:26

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

score 1 · Answer 4 · answered Mar 22 '22 at 15:11

1

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

answered Mar 22 '22 at 15:11

Dominique

13,061
14
45
83

I would skip `(Line[i] >= "A") and (Line[i] <= "Z")` and push all characters that are `b_inside` through `LCase(Line[i])` because there are foreign languages that have other letters than `A-Z` that need conversion to like german `ÄÜÖ`. • Now it would be intersting which of the 3 approaches is faster ;) – Pᴇʜ Mar 22 '22 at 15:20

In Excel with VB I couldn't change all occurrences between {MyText} to lowercase

4 Answers4