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:

bread	1 item 330 calories
apple sugar	300g 1232 calories
tost	110g 250 calories

<tbody>
</tbody>

etc...

All i want is text extraction like:
D1 column E1 column

1 item	330 calories
300 g	1232 calories
110g	250 calories

<tbody>
</tbody>

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

<colgroup><col><col><col><col><col></colgroup><tbody></tbody>

Αβοκάντος	Ένα/μία 315 Θερμίδες	Ένα/μία	315 Θερμίδες
Αβοκάντος (Φρέσκο Ωμό)	1 κιλό 1253 Θερμίδες	1 κιλό	1253 Θερμίδες
Αγγούρι	Ένα/μία 36 Θερμίδες	Ένα/μία	α 36 Θερμίδες
Αγγούρι raita (Ινδικό)	Κουτάλι Σούπας 20 Θερμίδες	Κουτάλι Σούπας	ς 20 Θερμίδες

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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