Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 4 down vote accepted

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

enter image description here

share|improve this answer
2  
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
1  
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
1  
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.

share|improve this answer
    
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

You must log in to answer this question.

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