1

I have some strings like avg_face_count and I need to convert them in avgFaceCount in Excel Basically I need to find every underscore in the string, remove it and replace the next underscore character in uppercase I tried https://superuser.com/questions/996712/excel-findreplace-lowercase-after-a-certain-character but it only work for one underscore

thanks

cristis
  • 77
  • 1
  • 6

1 Answers1

1

=SUBSTITUTE(PROPER(SUBSTITUTE(A20,"_"," "))," ","")

It first replaces _ for a space character. Then creates Caps for the first character after the space and finally removes the spaces.

If you truly only need the character after _ to be in caps, then use this Office 365 solution:

=LET(cell, A2,
     a,    SEQUENCE(LEN(cell)),
SUBSTITUTE(
           REDUCE( A20, a,
           LAMBDA( b,   c,
IF(MID(b,c,1)="_",
   LEFT(b,c-1)&UPPER(MID(b,c,2))&RIGHT(b,MAX(a)-c-1),
   b))),"_",""))
P.b
  • 8,293
  • 2
  • 10
  • 25
  • Awesome I just found this solution too, but thanks – cristis Feb 10 '23 at 17:32
  • I updated it with a solution that actually checks for `_` so in case of your example or if the string would contain spaces it would only change the character after a `_`. – P.b Feb 10 '23 at 18:21