I have a “Name” column which I want to split into “FirstName”, “LastName” – how can I do it?

Article contributed by Dave Rado

Word's sorting capability is fairly rudimentary, especially for those migrating to it from WordPerfect (though it's surprising how many people don't realize Word can sort paragraphs, not just tables – or maybe not so surprising, given where the item is in the menus! The ability to sort on word 2 in field 3 would certainly be very useful (in Excel as well). I believe there has been a lot of demand for this capability to be added, so maybe it's on the way ...

But there are various things you can do in the meantime. One is, write a macro. Another is to use Find and Replace to separate the text into fields (using tabs as the field separator). For instance, I often use the following trick if I'm emailed a spreadsheet with a “Name” column which I want to split into “FirstName”, “LastName”: The following assumes the column is in Excel, but of course it could just as easily be in a Word table.

1.

Select the column in Excel and paste it into a blank Word document.

2.

Select the column in Word (press Alt + Left mouse click), then press Ctrl+Spacebar to remove manual character formatting, and select Table + Convert Table to Text.

3.

Use Find and Replace to replace any spaces with single ones:

In the Find what box type: ^w 
   (or select the "Special" button and choose "White space)
In the Replace with box type [space] (i.e. press the spacebar)
Click Replace All

4.

Next, you want to replace the spaces preceding the last names with tabs, in order to be able to convert back to a table separated by tabs.

To allow for the possibility that, somewhere in the list, there might be some middle names or initials, select the “Use wildcards” check box in the Find and Replace dialog, and:

In the Find what box type: (<*) ([! ]@)^13
In the Replace with box type: \1^t\2^p
Click Replace All

That will give you the following result (with non-printing characters displayed):

  


For more on using wildcard searches see: Finding and replacing characters using wildcards.

Alternatively, if your original list was in the format:

Major, Alan P.

Then you don't need to use wildcards in order to insert the tabs in the right place

In the Find what box type: ,[space] (type a comma then press the spacebar)
In the Replace with box type ^t
Click Replace All
  

5.

Select the text (Ctrl+A) and select Table + Convert Text to Table.

You should find that the Word dialog suggests 2 columns and that text should be separated at tabs. If not that you don't have any rogue text or empty paragraphs selected at the end of the document.

If there were any middle names or initials in the list, I fix these later by creating a MiddleName column in Excel and putting these orphans into it.

6.

Type “FirstName” and “LastName” in the two cells in the top row of your new table.

Copy your Word table, Alt+Tab into Excel, insert an additional column to the right of the “Name” column, and paste.

Sounds convoluted and I guess it is, but it doesn't take long – I've fixed 1,000 record mail merge Data Sources in minutes using this method.