-3

I am currently working on an excel file that has the person's entire name and title in one cell, obviously this is a bad idea as far as data validation goes. I am wondering if there is a a way in VBA or Excel to break the column into 4.

The name column follows the format: Last name, Title(if they have one) First Name Middle Name (or initial).

This makes it really tricky since I can't just separate by column. Any help is greatly appreciated!

braX
  • 11,506
  • 5
  • 20
  • 33
JackRussel
  • 3
  • 3
  • What about names with multiple words? This is not actually possible. – SLaks Dec 06 '17 at 16:33
  • This seems like something you could accomplish with simple formulas, shouldn't need vba. – SuperStew Dec 06 '17 at 16:34
  • Any clue what formula I could use? – JackRussel Dec 06 '17 at 16:35
  • `Find`, `Mid`, `Left`, `Right` just a few formula examples – Scott Holtzman Dec 06 '17 at 16:38
  • I am not sure how those would work since the names are different for each record. Edit: I used =LEFT(A2,(FIND(", ",A2)-1)) to find the last name – JackRussel Dec 06 '17 at 16:38
  • There are many reasons why you should avoid it. Unless your target are just people from one country and the names have a pattern of input like, Name Surname. Please read [this](https://stackoverflow.com/q/672855/7690982), [this](https://stackoverflow.com/questions/620118/personal-names-in-a-global-application-what-to-store) and [this](https://stackoverflow.com/questions/46608272/validate-title-case-full-name-with-regex) – danieltakeshi Dec 06 '17 at 17:10

1 Answers1

0

given some text in cell A1 then in cell B1 enter the formula 

  LEFT(A1,FIND(" ",A1))

and in C1 enter

 RIGHT(A1,LEN(A1)-LEN(B1))

Copy B1:C1 to D1:E1

Now copy B1:E1 down to the end of the names in column A

Harassed Dad
  • 4,669
  • 1
  • 10
  • 12
  • That helped with the last name, Unfortunately, I am going to have manually input the others since the previous DBA put zero data validation in and now the column is a mess. – JackRussel Dec 06 '17 at 16:51
  • If you need to split into more columns then you can continue copying D1:E1 across, each pair chops out another word. – Harassed Dad Dec 06 '17 at 16:57