Extraction Formula or Array?
 

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.

Extraction Formula or Array?
 

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

Extraction Formula or Array?
 

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

Extraction Formula or Array?
 

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

Extraction Formula or Array?
 

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

Extraction Formula or Array?
 

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.

Extraction Formula or Array?
 

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

Extraction Formula or Array?
 

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.

Extraction Formula or Array?
 

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.

Extraction Formula or Array?
 

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

Extraction Formula or Array?
 

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.

Extraction Formula or Array?
 

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.

Extraction Formula or Array?
 

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.

Extraction Formula or Array?
 

thanks to all. I finally got this to work. As for the data/text option, the
numbers that I am using come about from a formula and are not just data so
when I use this it parses the formula and data. If anyone knows how to parse
it with formula, that would behelpful, otherwise, Elkar thatnks for the help
got it to work.
"Elkar" wrote:

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.

Extraction Formula or Array?
 

Glad you got it to work.

If you still want to pursue the Text to Columns option, then try this:

Copy your original data (column A)
Select a new column (column B)
Instead of Paste, use Paste Special
Select "Values"
Click OK
You can now use the "Text to Columns" feature on Column B

HTH,
Elkar



"A.S." wrote:

thanks to all. I finally got this to work. As for the data/text option, the
numbers that I am using come about from a formula and are not just data so
when I use this it parses the formula and data. If anyone knows how to parse
it with formula, that would behelpful, otherwise, Elkar thatnks for the help
got it to work.
"Elkar" wrote:

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.

Extraction Formula or Array?
 

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