Adding a Second Capital Letter after a Prefix

Question

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

Answer 1

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

For converting De-barge to DeBarge, use :

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