extract numbers from cell

[zubair99]

Hi,
I need to extract numirical numbers from the cells and move to it's next cell.

see need to extract data below

[TABLE="width: 500"]
<tbody>[TR]
[TD]PICKLES (V.GF) PICKLED VEGETABLES & OLIVES 8
[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]BATENGAN Bl FURN [V) OVEN-BAKED EGGPLANT AND FRESH TOMATOES SERVED WITH CHICKPEAS, CARAMELIZED ONION, MINT, CRUSHED GARLIC AND LEBANESE SPICES 17.5
[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]HUMMUS WITH BREAD (V, GF) 9.5
[/TD]
[TD]0.00[/TD]
[/TR]
</tbody>[/TABLE]




I need this.

[TABLE="width: 500"]
<tbody>[TR]
[TD]PICKLES (V.GF) PICKLED VEGETABLES & OLIVES[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]BATENGAN Bl FURN [V) OVEN-BAKED EGGPLANT AND FRESH TOMATOES SERVED WITH CHICKPEAS, CARAMELIZED ONION, MINT, CRUSHED GARLIC AND LEBANESE SPICES [/TD]
[TD]17.5[/TD]
[/TR]
[TR]
[TD]HUMMUS WITH BREAD (V, GF)[/TD]
[TD]9.5[/TD]
[/TR]
</tbody>[/TABLE]

Please replay asap thanks.

 

[puneet1011]

Hi,
The objective is to find the first number.

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")) will give you the position of the first number

=LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1 will give you the text

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1)+1-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") will give you the number

Hope it helps.
Puneet

 

[hiker95]

zubair99,

How about something like this?

Excel 2007
	A	B
1	PICKLES (V.GF) PICKLED VEGETABLES & OLIVES 8	8
2	BATENGAN Bl FURN [V) OVEN-BAKED EGGPLANT AND FRESH TOMATOES SERVED WITH CHICKPEAS, CARAMELIZED ONION, MINT, CRUSHED GARLIC AND LEBANESE SPICES 17.5	17.5
3	HUMMUS WITH BREAD (V, GF) 9.5	9.5
Sheet1

Cell Formulas
Range	Formula
B1	=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)+0

The formula in cell B1, copied down:

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)+0

 

[Peter_SSs]

I need this.

[TABLE="width: 500"]
[TR]
[TD]PICKLES (V.GF) PICKLED VEGETABLES & OLIVES[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]BATENGAN Bl FURN [V) OVEN-BAKED EGGPLANT AND FRESH TOMATOES SERVED WITH CHICKPEAS, CARAMELIZED ONION, MINT, CRUSHED GARLIC AND LEBANESE SPICES [/TD]
[TD]17.5[/TD]
[/TR]
[TR]
[TD]HUMMUS WITH BREAD (V, GF)[/TD]
[TD]9.5[/TD]
[/TR]
[/TABLE]

Try these two formulas, copied down.
I've assumed, like your samples, the number you want is always at the end, and there are no other numbers in your data.

Excel Workbook
	A	B	C
2	PICKLES (V.GF) PICKLED VEGETABLES & OLIVES 8	PICKLES (V.GF) PICKLED VEGETABLES & OLIVES	8
3	BATENGAN Bl FURN [V) OVEN-BAKED EGGPLANT AND FRESH TOMATOES SERVED WITH CHICKPEAS, CARAMELIZED ONION, MINT, CRUSHED GARLIC AND LEBANESE SPICES 17.5	BATENGAN Bl FURN [V) OVEN-BAKED EGGPLANT AND FRESH TOMATOES SERVED WITH CHICKPEAS, CARAMELIZED ONION, MINT, CRUSHED GARLIC AND LEBANESE SPICES	17.5
4	HUMMUS WITH BREAD (V, GF) 9.5	HUMMUS WITH BREAD (V, GF)	9.5
Extract Number

 

[Peter_SSs]

I've assumed ... there are no other numbers in your data.

If that is not always true, then here is an alternative formula for column B.

Excel Workbook
	A	B	C
2	PICKLES (V.GF, 18% Sugar) PICKLED VEGETABLES & OLIVES 8	PICKLES (V.GF, 18% Sugar) PICKLED VEGETABLES & OLIVES	8
Extract Number (2)

 

[zubair99]

If that is not always true, then here is an alternative formula for column B.

Extract Number (2)

*	A	B	C
PICKLES (V.GF, 18% Sugar) PICKLED VEGETABLES & OLIVES 8	PICKLES (V.GF, 18% Sugar) PICKLED VEGETABLES & OLIVES

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:464px;"><col style="width:464px;"><col style="width:57px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]8[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =LEFT(A2,FIND("ß",SUBSTITUTE(A2," ","ß",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1) C2 =RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)+0 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

I have one more pattern need to extract this.



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SANPELLEGRINO LIMONATA, ARAN Cl AT A ROSSA, CHINOTTO, MINERAL WATER (250ML) 4.5 MINERAL WATER (500ML) 7


[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]TEA PEPPERMINT, EARL GREY, ENGLISH BREAKFAST, GREEN, CHAMOMILE, VANILLA CHAI 4
[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]COFFEE 3.8/48
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

need it.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SANPELLEGRINO LIMONATA, ARAN Cl AT A ROSSA, CHINOTTO, MINERAL WATER (250ML) 4.5 MINERAL WATER (500ML) 7[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]TEA PEPPERMINT, EARL GREY, ENGLISH BREAKFAST, GREEN, CHAMOMILE, VANILLA CHAI 4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]COFFEE 3.8/48
[/TD]
[TD]3.8/48[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

I have one more pattern need to extract this.

need it.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SANPELLEGRINO LIMONATA, ARAN Cl AT A ROSSA, CHINOTTO, MINERAL WATER (250ML) 4.5 MINERAL WATER (500ML) 7[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]TEA PEPPERMINT, EARL GREY, ENGLISH BREAKFAST, GREEN, CHAMOMILE, VANILLA CHAI 4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]COFFEE 3.8/48
[/TD]
[TD]3.8/48[/TD]
[/TR]
</tbody>[/TABLE]

You are no longer necessarily extracting a number from the end, as that last example is text, not a number.

Try
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

 

[hiker95]

zubair99,

Have you tried my other formula in my reply #3?

How about something like this?

Excel 2007
	A	B	C
1	SANPELLEGRINO LIMONATA, ARAN Cl AT A ROSSA, CHINOTTO, MINERAL WATER (250ML) 4.5 MINERAL WATER (500ML) 7	7	7
2	TEA PEPPERMINT, EARL GREY, ENGLISH BREAKFAST, GREEN, CHAMOMILE, VANILLA CHAI 4	4	4
3	COFFEE 3.8/48	3.8/48	3.8/48
4
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))
C1	=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",300)),300))