3
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?
wizlog
Posted 2011-08-08T20:02:38.167
Reputation: 12 320
4
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
Ellesa
Posted 2011-08-08T20:02:38.167
Reputation: 9 729
6
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.
Lance Roberts
Posted 2011-08-08T20:02:38.167
Reputation: 7 895
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 – 2011-08-08T21:01:26.643
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 – 2011-08-08T21:04:06.950
I took a screen shot... then tried to have them execute. looked like this Why?
– wizlog – 2011-08-08T21:10:17.303@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 – 2011-08-08T21:16:12.267
Sorry. When you say "that cell" where (exactly)? – wizlog – 2011-08-08T21:18:02.923
@wizlog, now your confusing me, in your question you talked about the split you wanted, and referenced "and in another cell", so I assumed you had cells that you wanted your values in. I have no idea where you want those cells. – Lance Roberts – 2011-08-08T21:24:09.053
Thanks for your second formula Lance, i'm using it as the basis for something similar in my own Excel related question at (http://superuser.com/questions/366318/excel-2010-formula-to-strip-out-part-of-cells)
– HaydnWVN – 2011-12-09T13:09:52.273
2Good Catch on the variable length parameter. – Lance Roberts – 2011-08-08T20:55:33.733
How do I Ctrl + Shift + Enter? I just press them, then release, then press CTRL + V? – wizlog – 2011-08-08T21:04:36.577
1First, copy the formula, then paste it into the formula bar. Instead of pressing
Enterlike you normally do when entering data, pressCtrl+Shift+Enteraltogether. – Ellesa – 2011-08-08T21:13:47.673OK... After pressing that, I still don't get anything other than a #N/A – wizlog – 2011-08-08T21:16:44.360
1After 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 – 2011-08-08T21:25:51.043
OK... I wasn't pressing <Ctrl> + <Shift> + <Enter> while inside the cell... I just had the cell selected. – wizlog – 2011-08-08T21:30:04.907