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.