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

Question

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?

Answer 1

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

Answer 2

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.