Splitting names from one to two columns: what do others do in this situation?

[MsCynic]

Hi all,

I am tidying a spreadsheet in preparation for import into a CRM. All names are in one column.

In the past I have had good success with the Text to Columns function for splitting first and last names into separate cells. Most colums are uniform, like John Smith so the function works well on these.

However, this spreadsheet also has a large number of columns which have non-uniform names, such as John & Marsha Smith, or Dutch names like Mark van der Linden. I'm interested to know how others deal with data when presented with these sorts of names.

 

[Molden]

Hi all,

I am tidying a spreadsheet in preparation for import into a CRM. All names are in one column.

In the past I have had good success with the Text to Columns function for splitting first and last names into separate cells. Most colums are uniform, like John Smith so the function works well on these.

However, this spreadsheet also has a large number of columns which have non-uniform names, such as John & Marsha Smith, or Dutch names like Mark van der Linden. I'm interested to know how others deal with data when presented with these sorts of names.

Not sure how your data looks but I would think googling VBA excel split

would put you on the right path

 

[SyedUsman]

Dear Molden ,

Use the Excel Flash Fill feature by giving 2 or 3 examples & it will work fine.

 

[MsCynic]

Thanks for the replies.

I've uploaded a screenshot of my worksheet. http://i.imgur.com/8V9XXpI.png.

The 4th row I'd want Grant and Philippa to both to in the first name column. On the 12th row, I'd want Jaroen to go in the first column and van der Beek to be in the second column.

Sorry but I don't quite get how flash fill would do this in conjunction with the Text to Columns feature. Would you mind expanding on this, please?

 

[tonyyy]

MsCynic,

Sorry but I don't quite get how flash fill would do this in conjunction with the Text to Columns feature. Would you mind expanding on this, please?

Flash Fill is a feature added to Excel 2013 and later versions. Sadly I'm using Excel 2010 and have no experience with Flash Fill, but there's a three minute tutorial/video that makes it look easy to use.

Good luck!

tonyyy