excel problem extract text and numbers from text string

[pcbmaniac]

Hi all,

i 'm looking for solution to the following problem:

i have a string at B1 and C1 columns like this: [TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]bread[/TD]
[TD]1 item 330 calories[/TD]
[/TR]
[TR]
[TD]apple sugar[/TD]
[TD]300g 1232 calories[/TD]
[/TR]
[TR]
[TD]tost[/TD]
[TD]110g 250 calories[/TD]
[/TR]
</tbody>[/TABLE]

etc...

All i want is text extraction like:
D1 column E1 column
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1 item[/TD]
[TD]330 calories[/TD]
[/TR]
[TR]
[TD]300 g[/TD]
[TD]1232 calories[/TD]
[/TR]
[TR]
[TD]110g[/TD]
[TD]250 calories[/TD]
[/TR]
</tbody>[/TABLE]
How can i achieve this results?

TIA

 

[admiral100]

Hi,

Try this

D1 =LEFT(LEFT(C1,-1-LOOKUP(,-FIND(" ",C1,ROW($1:$99)))),-1-LOOKUP(,-FIND(" ",LEFT(C1,-1-LOOKUP(,-FIND(" ",C1,ROW($1:$99)))),ROW($1:$99))))

E1 =RIGHT(C1,13)

 

[AkaTrouble]

Hi,

Try this

D1 =LEFT(LEFT(C1,-1-LOOKUP(,-FIND(" ",C1,ROW($1:$99)))),-1-LOOKUP(,-FIND(" ",LEFT(C1,-1-LOOKUP(,-FIND(" ",C1,ROW($1:$99)))),ROW($1:$99))))

E1 =RIGHT(C1,13)

Great solution just slight improvement on your D1 formula

=(LEFT(RIGHT(C2,13),4)*1)&" Calories"

removes the space from 3 digit calorie counts

 

[admiral100]

Thanks AkaTrouble for the input .

 

[Rick Rothstein]

Here is another solution for you to consider...

D1: =TRIM(SUBSTITUTE(C1,E1,""))

E1: =TRIM(LEFT(RIGHT(SUBSTITUTE(C1," ",REPT(" ",100)),200),200))

 

[pcbmaniac]

I must say thank you all for the possible solutions.
I 'am Greek and i used the above tables just to make a simulation of real problem witch has greek words.
Here is the results so far:

Real Data above:
B1 C1 D1 E1
[TABLE="width: 931"]
<colgroup><col><col><col><col><col></colgroup><tbody></tbody>[/TABLE]
[TABLE="class: grid, width: 896"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Αβοκάντος[/TD]
[TD]Ένα/μία 315 Θερμίδες[/TD]
[TD]Ένα/μία[/TD]
[TD] 315 Θερμίδες[/TD]
[/TR]
[TR]
[TD]Αβοκάντος (Φρέσκο Ωμό)[/TD]
[TD]1 κιλό 1253 Θερμίδες[/TD]
[TD]1 κιλό[/TD]
[TD]1253 Θερμίδες[/TD]
[/TR]
[TR]
[TD]Αγγούρι[/TD]
[TD]Ένα/μία 36 Θερμίδες[/TD]
[TD]Ένα/μία[/TD]
[TD]α 36 Θερμίδες[/TD]
[/TR]
[TR]
[TD]Αγγούρι raita (Ινδικό)[/TD]
[TD]Κουτάλι Σούπας 20 Θερμίδες[/TD]
[TD]Κουτάλι Σούπας[/TD]
[TD]ς 20 Θερμίδες[/TD]
[/TR]
</tbody>[/TABLE]

As you can see, the 1st solution was almost perfect except the extra character it extracts like in Ε1 3rd row "α" and E1 4th row "ς"

If i elliminate this, the problem will be fixed!

TIA again

 

[pcbmaniac]

Here is another solution for you to consider...

D1: =TRIM(SUBSTITUTE(C1,E1,""))

E1: =TRIM(LEFT(RIGHT(SUBSTITUTE(C1," ",REPT(" ",100)),200),200))

YES SIR!
That's did the trick! Many thanks, you save me a lot of work!

 

[AlKey]

In D1 and copy down

[B]=TRIM(LEFT(C1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C1&1/17,FIND(" ",C1)))-1))[/B]

then in E1

[B]=TRIM(SUBSTITUTE(C1,D1,""))[/B]

Excel 2010
	B	C	D	E
1	bread	1 item 330 calories	1 item	330 calories
2	apple sugar	300g 1232 calories	300g	1232 calories
3	tost	110g 250 calories	110g	250 calories
Sheet7