2

I have one column that is asking for my customer's name, some of my customers put just one and others put several names (first, last, title, etc.) I am trying to change this column to split at the space. I found an answer on here that gave me the following formulas LEFT(C2, FIND(" ",C2,)) and =RIGHT(B2, LEN(B2)−FIND(" ", B2)) This is working for me BUT if there is no space (they only put their first name) then it won't split out to the first column making half my users not have a first name.

1 Answer 1

0

In the event that the user inputs one name with no spaces, the formula in the OP could be wrapped in an IFERROR formula.

IFERROR(TRIM(RIGHT(B2, LEN(B3)−FIND(" ", B2,))),"")

The IFERROR statement returns simply and empty string "" if there are no spaces. (No spaces returns an error because FIND() results in an error when it cannot find a space.) The formula returns the characters to the right of the space. If no spaces found, it simply returns the contents of B2. (Of course, there is no way for the computer to know if the single name entered is a first or last name, even tricky for humans.)

Something similar could be used to return the first name or just the name in the original string:

IFERROR(TRIM(LEFT(B2,FIND(" ", TRIM(B2)))),B2)

The only gotcha will be if there are several names. But this is a good starting point. A useful resource is the Formulas and Functions Help for the Mac apps—Keynote, Pages, Numbers, and iBooks Author. Look for Text Functions. Good function combinations can give the desired result. Work from the inside out. For example, TRIM(B2) will remove all leading and trailing spaces. Then, FIND(" ",TRIM(B2)) will look for the first space without having to worry if there was a leading space. Hope this helps!

Examples:
Table

2
  • Is there a way for Last Name to be left blank in the case of Barney? Apr 16, 2017 at 6:10
  • Sure, instead of returning the contents of the cell return an empty string. I'll adjust the formula.
    – bjbk
    Apr 16, 2017 at 11:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .