Splitting name string into columns

Hi Nick,

Provided all of your names strictly follow the pattern shown in your two examples, the separation is not all that difficult. The process will fail, though, with names such as James Earl Jones.

Ensuring each part begins with a capital adds complexity to the formula. Here, I've done that as a separate step. The formula in columns F, G and H can be combined with those in D, E and F if desired.

Formulas:

Column B is an auxiliary column. Its results are used in the formulas in the next three columns.

B2, and filled down: =LEN(A)-LEN(SUBSTITUTE(A," ","",))

This counts the number of spaces in the string in Column A.

C2 and filled down: =IF(B<2,"",LEFT(A,FIND(" ",A)-1))

IF returns a null string if the number of spaces in the string in A is less than 2. Otherwise, it passes control to LEFT (and FIND).

Find returns the position of the first space in the string in column A. 1 is subtracted to give the position of the last character to be included in the string placed in this column. LEFT returns the characters the first word.

D2, and filled down: =IF(B<2,LEFT(A,FIND(" ",A)-1),MID(A,FIND(" ",A)+1,FIND(" ",A,FIND(" ",A)+1)-FIND(" ",A)))

IF there are less that two spaces in the string, the 'else' part of the formula above is called to return everything before the first space.

*Note that this will return an error if there are no spaces in the string (eg. Beyoncé, or Sting). See below.

If there are two (or more) spaces, MID returns everything between the first and second space, using the resuts of the first FIND (following MID) to determine where to start, and the results of the second and third FINDs to detrmine how many characters to include.

E2, and filled down: =RIGHT(A,LEN(A)-(B+LEN(C&D))+(B-1))

This returns all characters to the right of the second space in the string in column A

*This will aso return an error if there are no spaces in column A.

Errors:

The two errors above can be trapped using IFERROR.

To do so, enclose each of the formulas with IFERROR, as below:

D2: =IFERROR(IF(B<2,LEFT(A,FIND(" ",A)-1),MID(A,FIND(" ",A)+1,FIND(" ",A,FIND(" ",A)+1)-FIND(" ",A))),"")

E2: =IFERROR(RIGHT(A,LEN(A)-(B+LEN(C&D))+(B-1)),"")

Capitalisation:

Formatting the cells as "Title" displays the initial etter of each word as a capital, but does not change the actual data in the cell. To do that, you'll need to use the UPPER() function.

In the example table, this is done in columns F, G and H, using the data in columns D, E and F respectively.

F2: =IF(LEN(C)>0,UPPER(LEFT(C,1))&RIGHT(C,LEN(C)-1),"")

This formula may be filled right to column H, and filed down each column.

Regards,

Barry

Nick,

This may do the trick for you:

you can hide columns B and C:

B1=SEARCH(" ", A1, 1)

C1=IFERROR(SEARCH(" ", A1, B1+1), 0)

D1=IF(AND(B1>0, C1>0), LEFT(A1, B1), "")

E1=IF(AND(B1>0, C1>0), MID(A1, B1, C1-B1), LEFT(A1, B1))

F1=IF(AND(B1>0, C1>0), RIGHT(A1, LEN(A1)-C1), RIGHT(A1, LEN(A1)-B1))

select B1 thru F1 and fill down as needed

Nick,

The problem with =LEN(A)-LEN(SUBSTITUTE(A," ","",)) seems to be that you have a trailing comma (argument delimiter) with no argument following it.

Deleting that comma, or adding a valid argument after the comma, should solve the problem.

Jerry

"The error message is "Argument 4 in SUBSTITUTE must be greater than or equal to 1""

My bad, Nick. In the formula bar, the formula looks like this:

and works just fine. But I forgot that when copied, then pasted into a text environment, the fina comma must be removed.

@Jerry: Thanks for stepping in and making the correction.

Regarding combining the formulas to acheve the capitalisation with the ones extracting each part of the name:

Here's an example, using the formulas in C2 and F2.

C2: =IF(B<2,"",LEFT(A,FIND(" ",A)-1))

F2: =IF(LEN(C)>0,UPPER(LEFT(C,1))&RIGHT(C,LEN(C)-1),"")

In C2, there are two possible results: if-TRUE: "", if-FALSE: LEFT(A,FIND(" ",A)-1)

Only the if-FALSE result requires capitalisation; if-TRUE, a null string, has no characters, so there is nothing to capitalise.

We can combine the capitalisation with the if-FALSE part of this formula.

Looking at F2: We can eliminate the condition in F2's formula, as well as the "" result if that condition is FALSE. Since we are combining this with the if-FALSE part of C2's formula, 'C' will always have some length.

So here's step 1 of the rewrite:

C2: IF(B<2,"",UPPER(LEFT(C,1))&RIGHT(C,LEN(C)-1))

All that's left to do is replace each occurence of "C" with the if-FALSE calculation from the original formula in C2:

C2: IF(B<2,"",UPPER(LEFT(LEFT(A,FIND(" ",A)-1),1))&RIGHT(LEFT(A,FIND(" ",A)-1),LEN(LEFT(A,FIND(" ",A)-1))-1))

For the other two formulas, the revision process is much the same: Determine what part of the first formula always requires applying capitalisation, replace that part with the action part of the capitalising formula " PPER(LEFT(C,1))&RIGHT(C,LEN(C)-1) ", then insert what you've replaced in place of the three Cs (or Ds or Es) in UPPER(LEFT(C,1))&RIGHT(C,LEN(C)-1).

Personally, I'd stick with the solution offered above, using columns B, C, D and E as auxiliary columns (which could be hidden), and F, G and H to contain the final results:

Regards,

Barry

Dear Apple,

Why does someone need to be a coder to do in Numbers what Excel can do in it's sleep?

Seriously. It's no wonder people don't think of Apple when they think of business.

Matthew

mancino_matthew writes:

"Dear Apple,"

Hi Matthew,

Dear Apple does not regularly monitor these user-to-user forums. Use the menu item Provide Numbers Feedback, found in the Numbers menu in Numbers, or click the link to send your message to Apple.

Regards,

Barry