When Text-to-Columns Fails

A functional Van der Merwe story

In a column of full names containing first, middle and last names, Excel’s Text-To-Columnsfunction fails with last names like “van der Merwe”. To get around this, using Names Manager, we’ll create our own text functions standing on the broad shoulders of some text functions already available in Excel: FIND, LEFT, LEN, MID, RIGHT, SUBSTITUTE, against a full name in cell $A2:

• Trim any leading/trailing spaces in Full Names to avoid errors
• NoSpaceLen uses SUBSTITUTE to replace spaces with nothing.
• LenFull returns the string length including spaces
• SpacesCount subtracts the NoSpaceLen from LenFull to return count of spaces in original
• NoMiddleName is true if there are less than 2 spaces
• SpaceOnePos returns position of first space in $A2
• SpaceTwoPos returns position of the second space: It uses SUBSTITUTE to replace the 2nd space found with a pipe character, and then FIND to return the pipe character’s position

Now, we can create functions to return First, Middle and Last names:

A traditional (non-Names Manager) middle name formula may seem like an easier approach – until you try to debug it:

=IF(ISERR(FIND(” “;$A2;1+FIND(” “;$A2)));””;MID($A2;FIND(” “;$A2)+1;FIND(” “;$A2;1+FIND(” “;$A2))-FIND(” “;$A2)-1))

NOTE: Both, like Text-To-Columns, will fail on “AB de Villers”. Just saying.

Jasper Cook

Bio: This article was written by Jasper Cook, Excel guru and trombonist of note. Call or email us if KRS and Jasper can assist you with your Excel: enquiries@krs.co.za