Extracting specific numbers from field.

[PETERC086]

Please help.

I am trying to find a formula that will extract numbers from one field and separate into three fields
like my bad example below, i have one field with three numbers ie 300x600x9mm i need to have the three sets of numbers placed in each field. ie first number 300 in second field, second number 600 in third field and 3rd number in fourth field.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
300 x 600 x 9mm 300 600 9

if any one has any ideas on how i could perform this task, it would be much appreciated. thanks

 

[Peter_SSs]

Re: Please Help, Extracting specific numbers from field.

Welcome to the MrExcel board!

Are they all mm?
If not, could you give a slightly larger sample and the expected results to show what sort of variety there is in your data?

 

[PETERC086]

Re: Please Help, Extracting specific numbers from field.

[TABLE="width: 154"]
<colgroup><col></colgroup><tbody>[TR]
[TD]hi thanks for your prompt rely!!

all the sizes are diffent with only the last number having mm against it please see below for details. thanks

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 154"]
<colgroup><col></colgroup><tbody>[TR]
[TD]300 X 300 X 9MM[/TD]
[/TR]
[TR]
[TD]300 X 300 X 9MM[/TD]
[/TR]
[TR]
[TD]300 X 600 X 9MM[/TD]
[/TR]
[TR]
[TD]300 X 600 X 9MM[/TD]
[/TR]
[TR]
[TD]600 X 600 X 9MM[/TD]
[/TR]
[TR]
[TD]600 X 600 X 9MM[/TD]
[/TR]
[TR]
[TD]300 X 400 X 7.8MM[/TD]
[/TR]
[TR]
[TD]300 X 600 X 8.5MM[/TD]
[/TR]
[TR]
[TD]130 X 790 X 12MM[/TD]
[/TR]
[TR]
[TD]190 X 790 X 12MM[/TD]
[/TR]
[TR]
[TD]130 X 790 X 12MM[/TD]
[/TR]
[TR]
[TD]190 X 790 X 12MM[/TD]
[/TR]
[TR]
[TD]300 x 600 x 9mm[/TD]
[/TR]
[TR]
[TD]300 x 600 x 9mm

Thanks again !! [/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Re: Please Help, Extracting specific numbers from field.

Here is a manual method that you could consider, but it does depend a little on the accuracy of your sample data (some rows have "x" between the numbers and some have "X"). For the moment I will assume those "x" values are either all upper case or all lower case.

1. Select the entire column by clicking its heading label
2. Find/Replace (Ctrl+H or in the Editing section of the Home ribbon)
3. Find what: MM Replace with: Leave blank Options>> Ensure 'Match entire cell contents'; is not checked -> Replace All -> OK -> Close
4. With the whole column still selected, Data ribbon tab -> Text to Columns -> Delimited -> Next -> In the 'Other' box put "X" or "x" depending on what the values are for you -> Finish

If you do in fact have a mixture of "X" and "x", do an extra Find/Replace between steps 3 and 4 above and replace "x" with "X"

 

[Rick Rothstein]

Re: Please Help, Extracting specific numbers from field.

For a formula solution, assuming your data starts in cell A1, put this in cell B1...

=0+TRIM(MID(SUBSTITUTE("x"&SUBSTITUTE(LOWER($A1),"mm",""),"x",REPT(" ",99)),COLUMNS($B:B)*99,99))

and copy it across to cell D1, then copy cells B1:D1 down to the bottom of your data.

 

[Peter_SSs]

Re: Please Help, Extracting specific numbers from field.

.. and you could eliminate a few superfluous functions/characters in there too

=0+TRIM(MID(SUBSTITUTE("x"&SUBSTITUTE(LOWER($A1),"mm",""),"x",REPT(" ",99)),COLUMNS($B:B)*99,99))

.. or another formula option is to have separate (simpler) formulas for each column

Excel Workbook
	A	B	C	D
1	300 X 300 X 9MM	300	300	9
2	300 X 300 X 9MM	300	300	9
3	300 X 600 X 9MM	300	600	9
4	300 X 600 X 9MM	300	600	9
5	600 X 600 X 9MM	600	600	9
6	600 X 600 X 9MM	600	600	9
7	300 X 400 X 7.8MM	300	400	7.8
8	300 X 600 X 8.5MM	300	600	8.5
9	130 X 790 X 12MM	130	790	12
10	190 X 790 X 12MM	190	790	12
11	130 X 790 X 12MM	130	790	12
12	190 X 790 X 12MM	190	790	12
13	300 x 600 x 9mm	300	600	9
14	300 x 600 x 9mm	300	600	9
Extract numbers 2

.. and if the sample data is accurate in that the "x" or "X" is always surrounded by spaces, it can be a bit simpler still.

Excel Workbook
	A	B	C	D
1	300 X 300 X 9MM	300	300	9
2	300 X 300 X 9MM	300	300	9
3	300 X 600 X 9MM	300	600	9
4	300 X 600 X 9MM	300	600	9
5	600 X 600 X 9MM	600	600	9
6	600 X 600 X 9MM	600	600	9
7	300 X 400 X 7.8MM	300	400	7.8
8	300 X 600 X 8.5MM	300	600	8.5
9	130 X 790 X 12MM	130	790	12
10	190 X 790 X 12MM	190	790	12
11	130 X 790 X 12MM	130	790	12
12	190 X 790 X 12MM	190	790	12
13	300 x 600 x 9mm	300	600	9
14	300 x 600 x 9mm	300	600	9
Extract numbers 3

 

[PETERC086]

Re: Please Help, Extracting specific numbers from field.

Thanks so much MrExcel MVP Peter_SSs
the last formula you supplied worked

you are a life saver!! your assistance is most appreciated.
Many thanks

Spreadsheet Formulas

Cell Formula B1 =LEFT(A1,FIND(" ",A1)-1)+0 C1 =MID(SUBSTITUTE(A1," ",REPT(" ",20)),40,20)+0 D1 =RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-2)," ",REPT(" ",20)),20)+0 <tbody> </tbody>

<tbody>
</tbody>

 

[Peter_SSs]

Re: Please Help, Extracting specific numbers from field.

You are very welcome. I think all the formulas posted should have worked, but of course you are welcome to choose the one(s) you prefer.
Thanks for letting us know.

 

[PETERC086]

Re: Please Help, Extracting specific numbers from field.

Hey...
need to ask another favour..
i have a list of the following codes
[TABLE="width: 147"]
<colgroup><col></colgroup><tbody>[TR]
[TD](JNOXXMCSSQOI)[/TD]
[/TR]
[TR]
[TD]JNOXXMCSSPTE[/TD]
[/TR]
[TR]
[TD](JNOXXMCSYPMI)[/TD]
[/TR]
[TR]
[TD](JNOXXMCSYQOI)[/TD]
[/TR]
[TR]
[TD](JNOXXMCSYPTE)[/TD]
[/TR]
[TR]
[TD](JNOXXMCYYPMI)[/TD]
[/TR]
[TR]
[TD](JNOXXMCYYQOI)[/TD]
[/TR]
[TR]
[TD](JNOXXMCYYPTE)[/TD]
[/TR]
[TR]
[TD](JNOXXMISSPMI)[/TD]
[/TR]
[TR]
[TD](JNOXXMISSQOI)[/TD]
[/TR]
[TR]
[TD](JNOXXMISSPTE)[/TD]
[/TR]
[TR]
[TD](JNOXXMISYPMI)[/TD]
[/TR]
[TR]
[TD](JNOXXMISYQOI)[/TD]
[/TR]
[TR]
[TD](JNOXXMISYPTE)[/TD]
[/TR]
[TR]
[TD](JNOXXMIYYPMI)[/TD]
[/TR]
[TR]
[TD](JNOXXMIYYQOI)
and want to ask if there is a formula to remove brackets ? thanks [/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Re: Please Help, Extracting specific numbers from field.

Hey...
need to ask another favour..
i have a list of the following codes
[TABLE="width: 147"]
<colgroup><col></colgroup><tbody>[TR]
[TD](JNOXXMCSSQOI)[/TD]
[/TR]
[TR]
[TD]JNOXXMCSSPTE[/TD]
[/TR]
[TR]
[TD](JNOXXMCSYPMI)[/TD]
[/TR]
[TR]
[TD](JNOXXMCSYQOI)[/TD]
[/TR]
[TR]
[TD](JNOXXMCSYPTE)[/TD]
[/TR]
[TR]
[TD](JNOXXMCYYPMI)[/TD]
[/TR]
[TR]
[TD](JNOXXMCYYQOI)[/TD]
[/TR]
[TR]
[TD](JNOXXMCYYPTE)[/TD]
[/TR]
[TR]
[TD](JNOXXMISSPMI)[/TD]
[/TR]
[TR]
[TD](JNOXXMISSQOI)[/TD]
[/TR]
[TR]
[TD](JNOXXMISSPTE)[/TD]
[/TR]
[TR]
[TD](JNOXXMISYPMI)[/TD]
[/TR]
[TR]
[TD](JNOXXMISYQOI)[/TD]
[/TR]
[TR]
[TD](JNOXXMISYPTE)[/TD]
[/TR]
[TR]
[TD](JNOXXMIYYPMI)[/TD]
[/TR]
[TR]
[TD](JNOXXMIYYQOI)
and want to ask if there is a formula to remove brackets ? thanks [/TD]
[/TR]
</tbody>[/TABLE]

Since the only place the parentheses appear is at the beginning and end of the text, a simple substitution should do it for you...

=SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")