Breaking a string of text
 

How do I break this into three columns using a formula? L(117),D(63),O(4)

Breaking a string of text
 

You don't really need a formula. Select the cell, then Data-Text to Columns.
Select delimited, click Next, check comma, click Finish.

"Confused" wrote:

How do I break this into three columns using a formula? L(117),D(63),O(4)

Breaking a string of text
 

I would like to only extract the numbers within the parentheses into three
columns.
My end result would be 117 63 4

"Vergel Adriano" wrote:

You don't really need a formula. Select the cell, then Data-Text to Columns.
Select delimited, click Next, check comma, click Finish.

"Confused" wrote:

How do I break this into three columns using a formula? L(117),D(63),O(4)

Breaking a string of text
 

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


"Confused" wrote:

I would like to only extract the numbers within the parentheses into three
columns.
My end result would be 117 63 4

"Vergel Adriano" wrote:

You don't really need a formula. Select the cell, then Data-Text to Columns.
Select delimited, click Next, check comma, click Finish.

"Confused" wrote:

How do I break this into three columns using a formula? L(117),D(63),O(4)

Breaking a string of text
 

On Thu, 8 Mar 2007 19:59:00 -0800, Confused
wrote:

How do I break this into three columns using a formula? L(117),D(63),O(4)

My answer to your previous question will also work he

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