Capitalization of cell upto first number

[mowens74]

Hello,
I am looking for a VBA that looks at the value of a cell and capatilizes all letters upto the first number.
So for example, a cell containing "cOuldBE463.now" would become "COULDBE463.now".
I have attached an example sheet with 4 different examples.

Thanking you in advance.

[alansidman]

Using Power Query, load your file to PQ. Split the column based upon the delimeter "Non Digit to Digit." Upper Case the split column. Merge the columns back together. Here is the Mcode for that action

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "Source", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Source.1", "Source.2"}),
    #"Uppercased Text" = Table.TransformColumns(#"Split Column by Character Transition",{{"Source.1", Text.Upper, type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Uppercased Text",{"Source.1", "Source.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

File attached for your review.

[mowens74]

alansidman: Thank you for your help, couldn't really follow what this code was doing other than it put the data into a table which is something I don't want to do. Thank you for your time and help.

Other two suggestions I understand a little more, have tried them and both work so thank you.

[nankw83]

Try the below code

Sub UpperCase()

For x = 2 To Range("A" & Rows.Count).End(xlUp).Row
    j = Cells(x, 1)
    For i = 1 To Len(j)
        If IsNumeric(Mid(j, i, 1)) Then Exit For Else Mid(j, i, 1) = UCase(Mid(j, i, 1))
    Next
    Cells(x, 1) = j
Next

End Sub

[Fluff13]

How about

Sub mowens()
   Dim Cl As Range
   Dim i As Long
   
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      For i = 1 To Len(Cl)
         If IsNumeric(Mid(Cl, i, 1)) Then
            Cl.Value = UCase(Left(Cl, i - 1)) & Mid(Cl, i)
            Exit For
         End If
         If i = Len(Cl) Then Cl = UCase(Cl)
      Next i
   Next Cl
End Sub

[Fluff13]

You're welcome & thanks for the feedback.

[alansidman]

Suggest you clcik on the link in my signature about Power Query