I need a formula or array that extracts numbers from a cell before and after
spaces (in the same cell), but the number of numbers in that cell varies, but
is always consecutive, but not in order:

Example Data: How I need it to look:
A1= 1 2 3 B1=1 C1=2 D1=3
A2= 5 6 9 8 10 7 B2=5 C2=6 D2=9 E2=8 F2=10 G2=7
A3 =4 7 6 5 B3=4 C3=7 D3=6 E3=5

Any help that can be provided would be appreciated.

How about using Data|Text to columns
Specify delmited (by spaces)

Not a formula, though.

A.S. wrote:

I need a formula or array that extracts numbers from a cell before and after
spaces (in the same cell), but the number of numbers in that cell varies, but
is always consecutive, but not in order:

Example Data: How I need it to look:
A1= 1 2 3 B1=1 C1=2 D1=3
A2= 5 6 9 8 10 7 B2=5 C2=6 D2=9 E2=8 F2=10 G2=7
A3 =4 7 6 5 B3=4 C3=7 D3=6 E3=5

Any help that can be provided would be appreciated.

--

Dave Peterson

Not sure what this is suppossed to make the data look like, but I don't think
this does the trick.

"Dave Peterson" wrote:

How about using Data|Text to columns
Specify delmited (by spaces)

Not a formula, though.

A.S. wrote:

I need a formula or array that extracts numbers from a cell before and after
spaces (in the same cell), but the number of numbers in that cell varies, but
is always consecutive, but not in order:

Example Data: How I need it to look:
A1= 1 2 3 B1=1 C1=2 D1=3
A2= 5 6 9 8 10 7 B2=5 C2=6 D2=9 E2=8 F2=10 G2=7
A3 =4 7 6 5 B3=4 C3=7 D3=6 E3=5

Any help that can be provided would be appreciated.

--

Dave Peterson

I assumed you wanted 1 in B1, 2 in C1, and 3 in D1.

Maybe I assumed wrong?

A.S. wrote:

Not sure what this is suppossed to make the data look like, but I don't think
this does the trick.

"Dave Peterson" wrote:

How about using Data|Text to columns
Specify delmited (by spaces)

Not a formula, though.

A.S. wrote:

I need a formula or array that extracts numbers from a cell before and after
spaces (in the same cell), but the number of numbers in that cell varies, but
is always consecutive, but not in order:

Example Data: How I need it to look:
A1= 1 2 3 B1=1 C1=2 D1=3
A2= 5 6 9 8 10 7 B2=5 C2=6 D2=9 E2=8 F2=10 G2=7
A3 =4 7 6 5 B3=4 C3=7 D3=6 E3=5

Any help that can be provided would be appreciated.

--

Dave Peterson


--

Dave Peterson

On Fri, 2 Feb 2007 09:10:01 -0800, A.S. wrote:

I need a formula or array that extracts numbers from a cell before and after
spaces (in the same cell), but the number of numbers in that cell varies, but
is always consecutive, but not in order:

Example Data: How I need it to look:
A1= 1 2 3 B1=1 C1=2 D1=3
A2= 5 6 9 8 10 7 B2=5 C2=6 D2=9 E2=8 F2=10 G2=7
A3 =4 7 6 5 B3=4 C3=7 D3=6 E3=5

Any help that can be provided would be appreciated.

Have you tried using the Data/Text to Columns wizard with <space as the
delimiter?


--ron

I tried this but it just put the formula. Maybe I'm not doin somethin right.
Can you please help as far ashowing how to do this with data/text?

"Ron Rosenfeld" wrote:

On Fri, 2 Feb 2007 09:10:01 -0800, A.S. wrote:

I need a formula or array that extracts numbers from a cell before and after
spaces (in the same cell), but the number of numbers in that cell varies, but
is always consecutive, but not in order:

Example Data: How I need it to look:
A1= 1 2 3 B1=1 C1=2 D1=3
A2= 5 6 9 8 10 7 B2=5 C2=6 D2=9 E2=8 F2=10 G2=7
A3 =4 7 6 5 B3=4 C3=7 D3=6 E3=5

Any help that can be provided would be appreciated.

Have you tried using the Data/Text to Columns wizard with <space as the
delimiter?


--ron

On Fri, 2 Feb 2007 10:10:01 -0800, A.S. wrote:

I tried this but it just put the formula. Maybe I'm not doin somethin right.
Can you please help as far ashowing how to do this with data/text?

"Ron Rosenfeld" wrote:

On Fri, 2 Feb 2007 09:10:01 -0800, A.S. wrote:

I need a formula or array that extracts numbers from a cell before and after
spaces (in the same cell), but the number of numbers in that cell varies, but
is always consecutive, but not in order:

Example Data: How I need it to look:
A1= 1 2 3 B1=1 C1=2 D1=3
A2= 5 6 9 8 10 7 B2=5 C2=6 D2=9 E2=8 F2=10 G2=7
A3 =4 7 6 5 B3=4 C3=7 D3=6 E3=5

Any help that can be provided would be appreciated.

Have you tried using the Data/Text to Columns wizard with <space as the
delimiter?


--ron


OK. From what you have written, I assumed that cell A1 contains a space
separated string:

1 2 3

And what you want is for

B1 to contain 1
C1 to contain 2
D1 to contain 3

To accomplish this, first select A1:An

Then, from the top menu, select Data and, from the drop-down submenu, "Text to
Columns.

At the Step 1 box, select Delimited; then Next
Step 2 box, select SPACE as a delimiter, then Next
Step 3 box, select FINISH

This will result in

A1 contains 1
B1 contains 2
C1 contains 3

and appropriate entries in other rows.

You can then either select and drag everything over one cell; or select column
A and insert one column.


--ron

On Fri, 2 Feb 2007 10:10:01 -0800, A.S. wrote:

I tried this but it just put the formula. Maybe I'm not doin somethin right.
Can you please help as far ashowing how to do this with data/text?

"Ron Rosenfeld" wrote:

On Fri, 2 Feb 2007 09:10:01 -0800, A.S. wrote:

I need a formula or array that extracts numbers from a cell before and after
spaces (in the same cell), but the number of numbers in that cell varies, but
is always consecutive, but not in order:

Example Data: How I need it to look:
A1= 1 2 3 B1=1 C1=2 D1=3
A2= 5 6 9 8 10 7 B2=5 C2=6 D2=9 E2=8 F2=10 G2=7
A3 =4 7 6 5 B3=4 C3=7 D3=6 E3=5

Any help that can be provided would be appreciated.

Have you tried using the Data/Text to Columns wizard with <space as the
delimiter?


--ron


I see now that you have a formula in A1, and not 1 2 3 as you had written.

To parse this out, do the following.

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

Then, in B1, use this formula:

=REGEX.MID($A18,"\d+",COLUMNS($A:A))

Copy/drag across as far as required.


--ron

Here's a formula that should work for you. Place in cell B1, then copy down
and/or right as far as needed. It may look ugly, but it works.

=IF($A1="","",IF(ISERROR(FIND("
",$A1)),IF(COLUMN()=2,$A1,""),IF(LEN($A1)-LEN(SUBSTITUTE($A1,"
",""))<COLUMN()-2,"",MID($A1,IF(COLUMN()=2,1,FIND("~",SUBSTITUTE($ A1,"
","~",COLUMN()-2))+1),IF(COLUMN()=2,FIND(" ",$A1),FIND("~",SUBSTITUTE($A1&"
"," ","~",COLUMN()-1))-FIND("~",SUBSTITUTE($A1," ","~",COLUMN()-2)))))))

HTH,
Elkar



"A.S." wrote:

I need a formula or array that extracts numbers from a cell before and after
spaces (in the same cell), but the number of numbers in that cell varies, but
is always consecutive, but not in order:

Example Data: How I need it to look:
A1= 1 2 3 B1=1 C1=2 D1=3
A2= 5 6 9 8 10 7 B2=5 C2=6 D2=9 E2=8 F2=10 G2=7
A3 =4 7 6 5 B3=4 C3=7 D3=6 E3=5

Any help that can be provided would be appreciated.

All I got was a blank cell. I put it next to the cell that contained "1 2 3"
and ended up with a blank cell, did I do something wrong? Did this work for
you?

"Elkar" wrote:

Here's a formula that should work for you. Place in cell B1, then copy down
and/or right as far as needed. It may look ugly, but it works.

=IF($A1="","",IF(ISERROR(FIND("
",$A1)),IF(COLUMN()=2,$A1,""),IF(LEN($A1)-LEN(SUBSTITUTE($A1,"
",""))<COLUMN()-2,"",MID($A1,IF(COLUMN()=2,1,FIND("~",SUBSTITUTE($ A1,"
","~",COLUMN()-2))+1),IF(COLUMN()=2,FIND(" ",$A1),FIND("~",SUBSTITUTE($A1&"
"," ","~",COLUMN()-1))-FIND("~",SUBSTITUTE($A1," ","~",COLUMN()-2)))))))

HTH,
Elkar



"A.S." wrote:

I need a formula or array that extracts numbers from a cell before and after
spaces (in the same cell), but the number of numbers in that cell varies, but
is always consecutive, but not in order:

Example Data: How I need it to look:
A1= 1 2 3 B1=1 C1=2 D1=3
A2= 5 6 9 8 10 7 B2=5 C2=6 D2=9 E2=8 F2=10 G2=7
A3 =4 7 6 5 B3=4 C3=7 D3=6 E3=5

Any help that can be provided would be appreciated.

Yes, this does work for me. A couple things to check. Your cell that
contains "1 2 3" is cell A1 right? And the cell you placed the formula in is
cell B1?

Also, make sure none of the spaces got left out of the formula. Often when
a long formula is posted to these discussion groups, it won't fit on a single
line, so line breaks are often inserted where there are spaces. I'm not sure
what the formula looks like on your screen, but at each line break there
should be a space following the quotes.

HTH,
Elkar

"A.S." wrote:

All I got was a blank cell. I put it next to the cell that contained "1 2 3"
and ended up with a blank cell, did I do something wrong? Did this work for
you?

"Elkar" wrote:

Here's a formula that should work for you. Place in cell B1, then copy down
and/or right as far as needed. It may look ugly, but it works.

=IF($A1="","",IF(ISERROR(FIND("
",$A1)),IF(COLUMN()=2,$A1,""),IF(LEN($A1)-LEN(SUBSTITUTE($A1,"
",""))<COLUMN()-2,"",MID($A1,IF(COLUMN()=2,1,FIND("~",SUBSTITUTE($ A1,"
","~",COLUMN()-2))+1),IF(COLUMN()=2,FIND(" ",$A1),FIND("~",SUBSTITUTE($A1&"
"," ","~",COLUMN()-1))-FIND("~",SUBSTITUTE($A1," ","~",COLUMN()-2)))))))

HTH,
Elkar



"A.S." wrote:

I need a formula or array that extracts numbers from a cell before and after
spaces (in the same cell), but the number of numbers in that cell varies, but
is always consecutive, but not in order:

Example Data: How I need it to look:
A1= 1 2 3 B1=1 C1=2 D1=3
A2= 5 6 9 8 10 7 B2=5 C2=6 D2=9 E2=8 F2=10 G2=7
A3 =4 7 6 5 B3=4 C3=7 D3=6 E3=5

Any help that can be provided would be appreciated.

You right it was the line breaks. OK, looks like we are on the right track.
ow, it is giving me 2 less than the total number of numbers in the cell, so
here is sample data with the formula as it is now, it appears to be starting
with the 3rd number from the left, if there is one, and goes from the

C5=1 3 2, D5=2
C6=7 6 5 4, D6=5, E6=4
C7=25 27 26, D7=26
C8=8 D8=Blank, since there is no 3rd #
C9=10 23 21 9 12 11... D9=21, E9=9, F9=12, G9=11...

Any thoughts on how we can make sure to get all the numbers? Thanks for the
help.


"Elkar" wrote:

Yes, this does work for me. A couple things to check. Your cell that
contains "1 2 3" is cell A1 right? And the cell you placed the formula in is
cell B1?

Also, make sure none of the spaces got left out of the formula. Often when
a long formula is posted to these discussion groups, it won't fit on a single
line, so line breaks are often inserted where there are spaces. I'm not sure
what the formula looks like on your screen, but at each line break there
should be a space following the quotes.

HTH,
Elkar

"A.S." wrote:

All I got was a blank cell. I put it next to the cell that contained "1 2 3"
and ended up with a blank cell, did I do something wrong? Did this work for
you?

"Elkar" wrote:

Here's a formula that should work for you. Place in cell B1, then copy down
and/or right as far as needed. It may look ugly, but it works.

=IF($A1="","",IF(ISERROR(FIND("
",$A1)),IF(COLUMN()=2,$A1,""),IF(LEN($A1)-LEN(SUBSTITUTE($A1,"
",""))<COLUMN()-2,"",MID($A1,IF(COLUMN()=2,1,FIND("~",SUBSTITUTE($ A1,"
","~",COLUMN()-2))+1),IF(COLUMN()=2,FIND(" ",$A1),FIND("~",SUBSTITUTE($A1&"
"," ","~",COLUMN()-1))-FIND("~",SUBSTITUTE($A1," ","~",COLUMN()-2)))))))

HTH,
Elkar



"A.S." wrote:

I need a formula or array that extracts numbers from a cell before and after
spaces (in the same cell), but the number of numbers in that cell varies, but
is always consecutive, but not in order:

Example Data: How I need it to look:
A1= 1 2 3 B1=1 C1=2 D1=3
A2= 5 6 9 8 10 7 B2=5 C2=6 D2=9 E2=8 F2=10 G2=7
A3 =4 7 6 5 B3=4 C3=7 D3=6 E3=5

Any help that can be provided would be appreciated.

Check that as you get towards the end of the text to columns process you
haven't flagged some of the columns as to be ignored. Look at the column
headers where the formats are specified.
The processs works fine for me.
--
David Biddulph

"A.S." wrote in message
...
You right it was the line breaks. OK, looks like we are on the right
track.
ow, it is giving me 2 less than the total number of numbers in the cell,
so
here is sample data with the formula as it is now, it appears to be
starting
with the 3rd number from the left, if there is one, and goes from the

C5=1 3 2, D5=2
C6=7 6 5 4, D6=5, E6=4
C7=25 27 26, D7=26
C8=8 D8=Blank, since there is no 3rd #
C9=10 23 21 9 12 11... D9=21, E9=9, F9=12, G9=11...

Any thoughts on how we can make sure to get all the numbers? Thanks for
the
help.


"Elkar" wrote:

Yes, this does work for me. A couple things to check. Your cell that
contains "1 2 3" is cell A1 right? And the cell you placed the formula
in is
cell B1?

Also, make sure none of the spaces got left out of the formula. Often
when
a long formula is posted to these discussion groups, it won't fit on a
single
line, so line breaks are often inserted where there are spaces. I'm not
sure
what the formula looks like on your screen, but at each line break there
should be a space following the quotes.

HTH,
Elkar

"A.S." wrote:

All I got was a blank cell. I put it next to the cell that contained "1
2 3"
and ended up with a blank cell, did I do something wrong? Did this work
for
you?

"Elkar" wrote:

Here's a formula that should work for you. Place in cell B1, then
copy down
and/or right as far as needed. It may look ugly, but it works.

=IF($A1="","",IF(ISERROR(FIND("
",$A1)),IF(COLUMN()=2,$A1,""),IF(LEN($A1)-LEN(SUBSTITUTE($A1,"
",""))<COLUMN()-2,"",MID($A1,IF(COLUMN()=2,1,FIND("~",SUBSTITUTE($ A1,"
","~",COLUMN()-2))+1),IF(COLUMN()=2,FIND("
",$A1),FIND("~",SUBSTITUTE($A1&"
"," ","~",COLUMN()-1))-FIND("~",SUBSTITUTE($A1,"
","~",COLUMN()-2)))))))

HTH,
Elkar



"A.S." wrote:

I need a formula or array that extracts numbers from a cell before
and after
spaces (in the same cell), but the number of numbers in that cell
varies, but
is always consecutive, but not in order:

Example Data: How I need it to look:
A1= 1 2 3 B1=1 C1=2 D1=3
A2= 5 6 9 8 10 7 B2=5 C2=6 D2=9 E2=8 F2=10 G2=7
A3 =4 7 6 5 B3=4 C3=7 D3=6 E3=5

Any help that can be provided would be appreciated.

I tested the formula out to fifteen numbers of varying length (1 to 4
digits), and it worked just fine. Here's one example line:

A1: 152 2 5248 25 4 3 98 102 6 78 1045 15 98 14

All numbers were extracted correctly in cells B1 through O1.

If there's still a problem, I'd suggest double checking the formula. Maybe
a space is still left out, or an extra one was added in?

HTH,
Elkar

"A.S." wrote:

You right it was the line breaks. OK, looks like we are on the right track.
ow, it is giving me 2 less than the total number of numbers in the cell, so
here is sample data with the formula as it is now, it appears to be starting
with the 3rd number from the left, if there is one, and goes from the

C5=1 3 2, D5=2
C6=7 6 5 4, D6=5, E6=4
C7=25 27 26, D7=26
C8=8 D8=Blank, since there is no 3rd #
C9=10 23 21 9 12 11... D9=21, E9=9, F9=12, G9=11...

Any thoughts on how we can make sure to get all the numbers? Thanks for the
help.


"Elkar" wrote:

Yes, this does work for me. A couple things to check. Your cell that
contains "1 2 3" is cell A1 right? And the cell you placed the formula in is
cell B1?

Also, make sure none of the spaces got left out of the formula. Often when
a long formula is posted to these discussion groups, it won't fit on a single
line, so line breaks are often inserted where there are spaces. I'm not sure
what the formula looks like on your screen, but at each line break there
should be a space following the quotes.

HTH,
Elkar

"A.S." wrote:

All I got was a blank cell. I put it next to the cell that contained "1 2 3"
and ended up with a blank cell, did I do something wrong? Did this work for
you?

"Elkar" wrote:

Here's a formula that should work for you. Place in cell B1, then copy down
and/or right as far as needed. It may look ugly, but it works.

=IF($A1="","",IF(ISERROR(FIND("
",$A1)),IF(COLUMN()=2,$A1,""),IF(LEN($A1)-LEN(SUBSTITUTE($A1,"
",""))<COLUMN()-2,"",MID($A1,IF(COLUMN()=2,1,FIND("~",SUBSTITUTE($ A1,"
","~",COLUMN()-2))+1),IF(COLUMN()=2,FIND(" ",$A1),FIND("~",SUBSTITUTE($A1&"
"," ","~",COLUMN()-1))-FIND("~",SUBSTITUTE($A1," ","~",COLUMN()-2)))))))

HTH,
Elkar



"A.S." wrote:

I need a formula or array that extracts numbers from a cell before and after
spaces (in the same cell), but the number of numbers in that cell varies, but
is always consecutive, but not in order:

Example Data: How I need it to look:
A1= 1 2 3 B1=1 C1=2 D1=3
A2= 5 6 9 8 10 7 B2=5 C2=6 D2=9 E2=8 F2=10 G2=7
A3 =4 7 6 5 B3=4 C3=7 D3=6 E3=5

Any help that can be provided would be appreciated.