CODEWITHSUNDEEP
excelreplacecasing
Miaka3
Miaka3

Reputation: 405

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

Upvotes: 1

Views: 63

Answers (1)

cyboashu
cyboashu

Reputation: 10433

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)

Upvotes: 2

Related Questions