Search code examples
excel-formulastr-replace

Excel replace underscore with next character in uppercase and remove underscore

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

Solution

  • =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))),"_",""))