Excel / VBA - Splitting and combining data


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.


The formula in A2 will be     
formula lin A3 will be     
you can copy the formula along the rows

Thanks to venkat1926 for this tip on the forum.
Published by aakai1056. Latest update on June 26, 2012 at 12:05 PM by Jeff.
This document, titled "Excel / VBA - Splitting and combining data," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).
VBA - Duplicate a value n number of times
Excel - A Count if macro based on multiple criteria