Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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. –  Ellesa 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. –  Ellesa 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.