Add a sentence case with numbers

[Manolocs]

Hello, I am using this formula to modify all text of a column to sentence case, but I have some cells that the text starts with numbers.
Is there a way to make the first Letter after the number in Upper Case?
Thank you in advance.....

=UPPER(LEFT(B1,1))&LOWER(RIGHT(B1,LEN(B1)-1))

 

[Manolocs]

@oldbrewer It is working! great thank you that is what I was looking for

 

[ask2tsp]

please copy the following code to a module

' First letter Uppercase, all following lowercase
' ignore digits at the beginning

   Dim textLength As Long
   Dim pos        As Long
   Dim result     As String
   
   textLength = Len(text): result = ""
   
   pos = 1
   While pos <= textLength _
     And ((Mid(text, pos, 1) >= "0" And Mid(text, pos, 1) <= "9") _
               Or Mid(text, pos, 1) = " ")
     
      pos = pos + 1
   Wend
   
   If pos > textLength Then
      result = text
      
   Else
      result = Left(text, pos - 1) + UCase(Mid(text, pos, 1))

      If pos < textLength Then
         result = result + LCase(Right(text, textLength - pos))
      End If
   End If
   
   properCase = result
End Function

and then enter the formula

=properCase(cell to modify)

in every cel you need

 

[oldbrewer]

I cheated in that I raised a post on here to find out why trailing spaces disappeared - they don't if you keep them in code rather than assigning to a cell in the spreadsheet - also I had in my macro something like cells(1,1)=cells(1,2)&cells(1,3) - this would not compile UNTIL I inserted a space either side of the &