Excel / VBA - Splitting and combining data

December 2016



Issue


I have 200Kb of data that need to be organized:
  • a1: Dr. John G. James and Mel James
  • a2: John H. James
  • a3: James X. Brian and Timothy F. Call Ph D
  • I need to split .. first, middle, last name and suffix into separate column.
  • If there are 2 names, I have to combine the first name and middle name into first name column (ex. John G. and Mel) and last name on the last name column.

Solution


The formula in A2 will be     
=LEFT(A1,SEARCH("and",A1)-1)     
formula lin A3 will be     
=RIGHT(A1,LEN(A1)-SEARCH("and",A1)-3)     
you can copy the formula along the rows



Thanks to venkat1926 for this tip on the forum.

Related :

This document entitled « Excel / VBA - Splitting and combining data » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.