Search code examples
excelreplacecasing

Adding a Second Capital Letter after a Prefix

I have an Excel sheet (MS Excel 2010) with a column of city names (Cell A2:A4) each name is conjoined as one one whole word with a leading Name Prefix (i.e., De, La, Las, Ja, etc.). I also have a range of Keywords (Cell B2:B4) that I would like to search (non-case sensitive) the beginning/start of each whole word (Cell A2:A4) that matches prefix in Cell B2:B4. If there's a beginning prefix match to Upper case the first letter of the Prefix and Upper Case the first letter following the Prefix and all other words in lower case, all as one word.

(Cell: A2)  Debarge  (Cell: B2) de  (Output Cell: C2) DeBarge

(Cell: A3)  dewitt  (Cell: B3) de  (Output Cell: C3) DeWitt

(Cell: A4)  Laplata  (Cell: B4) la  (Output Cell: C4) LaPlata

Thanks

Solution

  • This should work : =IF(PROPER(B1) &PROPER(SUBSTITUTE(UPPER(A1),UPPER(B1),"",1)) =A1,PROPER(B1) &PROPER(SUBSTITUTE(UPPER(A1),UPPER(B1),"",1)),A1)

    enter image description here

    For converting De-barge to DeBarge, use :

    =IFERROR(PROPER(MID(A1,1,FIND("-",A1)-1)) & PROPER(MID(A1,FIND("-",A1)+1,LEN(A1))),A1)