# Split a string in a cell in Excel using numbers as the divider

Example cell value: President & Founder, Millennium Pain Center, 1998– present.

I'd want that parsed (split) into "President & Founder, Millennium Pain Center," and in another cell, " 1998– present."

Is this doable?

-

It's doable with an array formula. Here's a solution based on an algorithm on the MS Office website. I modified the formula to take string length into consideration so that you can use it with other phrases.

Assuming A1 contains your string ("President & Founder, Millennium Pain Center, 1998– present"), Ctrl + Shift + Enter this in B1:

``````=MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("\$1:\$"&LEN(A1))),1)),0)
``````

It will return the index of the first number that occurs in the string. For the example, it will return 46.

Enter this (as a normal formula) in C1:

``````=MID(A1,1,B1-1)
``````

It will return President & Founder, Millennium Pain Center,

Enter this (as a normal formula) in D1:

``````=RIGHT(A1,LEN(A1)-B1+1)
``````

It will return 1998– present

-
Good Catch on the variable length parameter. – Lance Roberts Aug 8 '11 at 20:55
How do I Ctrl + Shift + Enter? I just press them, then release, then press CTRL + V? – wizlog Aug 8 '11 at 21:04
First, copy the formula, then paste it into the formula bar. Instead of pressing `Enter` like you normally do when entering data, press `Ctrl`+`Shift`+`Enter` altogether. – Kaze Aug 8 '11 at 21:13
OK... After pressing that, I still don't get anything other than a #N/A – wizlog Aug 8 '11 at 21:16
After pressing those keys, the formula should get enclosed in braces ("{ }"). That's how you can tell it's been entered as an array formula. – Kaze Aug 8 '11 at 21:25

These will work, they are array formulas, so you must use CTRL-SHIFT-ENTER when you enter them in the cell. The number \$40 just needs to be big enough for your largest string:

``````=LEFT(A1, MATCH(TRUE,ISNUMBER(1*MID(A1, ROW(\$1:\$40), 1)),0)-1)
``````

and

``````=RIGHT(A1, LEN(A1) - MATCH(TRUE,ISNUMBER(1*MID(A1, ROW(\$1:\$40), 1)),0) +1)
``````

The MID statement is creating an array of all the characters in the string.
The ISNUMBER statement then turns that into a TRUE/FALSE array.
The MATCH statement then finds the first TRUE, so therefore the first number.
Then it's just a matter of parsing.

-
I'm getting a #N/A error. I don't know how to make this work. Assuming the line I want to parse, is in A1. Where does the =LEFT and = RIGHT go? How can I then parse in Excel? – wizlog Aug 8 '11 at 21:01
The =LEFT is for the left side of the split you want, so put it in that cell. The =RIGHT is for the right side of the split you want, so put it in that cell. My formula does the parsing, I was just explaining. – Lance Roberts Aug 8 '11 at 21:04
I took a screen shot... then tried to have them execute. looked like this Why? – wizlog Aug 8 '11 at 21:10
@wizlog, you put the same picture both times in the link so I can't see the result, but realize you're using Kaze's method, and I only tested with mine. – Lance Roberts Aug 8 '11 at 21:16
Sorry. When you say "that cell" where (exactly)? – wizlog Aug 8 '11 at 21:18