1

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

cyboashu
  • 10,196
  • 2
  • 27
  • 46
Miaka3
  • 339
  • 1
  • 10
  • 27

1 Answers1

2

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)
cyboashu
  • 10,196
  • 2
  • 27
  • 46
  • Thank you aa_dd, the formula provided works very well and is able to do exactly what I was looking for... Thank You. Do you have any formula solutions for the same scenario, but only there's a hyphen separating the prefix and suffix (i.e., De-barge, de-Witt, La-Plata). Remove hyphen, and Proper Case each as one word (i.e. DeBarge, DeWitt, etc.) – Miaka3 Dec 10 '16 at 00:42
  • AWESOME - Works like a charm, thank you, after countless hours on my own efforts, this is greatly appreciated... thanks again. – Miaka3 Dec 10 '16 at 01:03