Extraction Formula or Array?

[A.S.]

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]

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

Not a formula, though.

 

[A.S.]

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

 

[Ron Rosenfeld]

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

 

[A.S.]

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?

 

[Elkar]

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

 

[Ron Rosenfeld]

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?

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

 

[A.S.]

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]

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

 

[Dave Peterson]

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

Maybe I assumed wrong?

 

[A.S.]

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 there:

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.

 

[David Biddulph]

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.

 

[Elkar]

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.]

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]

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

 

[Ron Rosenfeld]

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?

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