Contact KRS: +27 (0) 21 681 2900 | enquiries@krs.co.za | Contact Us

Musings

When Text-to-Columns Fails

11 May 2016 by Jasper Cook

A functional Van der Merwe story

Text to columns

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:

Text to columns

  • 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:

Text to columns

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

Load more comments
Thank you for the comment! Your comment must be approved first
comment-avatar