Breaking a string of text

  • Thread starter Thread starter Guest
  • Start date Start date
You don't really need a formula. Select the cell, then Data->Text to Columns.
Select delimited, click Next, check comma, click Finish.
 
I would like to only extract the numbers within the parentheses into three
columns.
My end result would be 117 63 4
 
ok,this is probably not the best solution but I hope it works for you. I'm
assuming you have a lot of these strings of text that it's not practical to
simply manually do it. I'm also assuming that the strings are in Column A
and that there will always be 3 numbers, each one of them enclosed in open
and close parenthesis. So

1.Define the following Names (Insert->Name->Define)

theString
=INDIRECT(ADDRESS(ROW(), 1))

FirstNumStart
=FIND("(", theString, 1)+1

FirstNumEnd
=FIND(")", theString,FirstNumStart)-FirstNumStart

FirstNumber
=MID(theString,FirstNumStart,FirstNumEnd)

SecondNumStart
=FIND("(", theString, FirstNumStart)+1

SecondNumEnd
=FIND(")", theString,SecondNumStart)-SecondNumStart

SecondNumber
=MID(theString,SecondNumStart,SecondNumEnd)

ThirdNumStart
=FIND("(", theString, SecondNumStart)+1

ThirdNumEnd
=FIND(")", theString,ThirdNumStart)-ThirdNumStart

ThirdNumber
=MID(theString,ThirdNumStart,ThirdNumEnd)

2. Now, in column B enter
=FirstNumber

3. Column C, enter:
=SecondNumber

4. Column D, enter:
=ThirdNumber
 
How do I break this into three columns using a formula? L(117),D(63),O(4)
Click to expand...

My answer to your previous question will also work here:

Here's one way with formulas:

Download and install Longre's free and easily distributable morefunc.xll add-in
from http://xcell05.free.fr

Then use this Regular Expression formula:

A2: =REGEX.MID($A1,"(?<=\()\d+(?=\))",COLUMNS($A:A))

Copy/drag across to column D
Copy/drag down as far as needed.

It picks out sequential integer numbers that are delineated by parentheses.

With regard to your A3 example, I note that you do NOT show the "1" as being
extracted, and I'm not sure of which columns for the location of 3 and 119. I
put them in the first two columns. If this is wrong, please post back with
more detail.
--ron
--ron
 
Back
Top