Extract Text or Number from a Cell

[dcecilia]

I have a file of 2000 rows I would like to Extract from each Cell the size of the product.

Example

Chlorella Powder 100g Organic <tbody> </tbody>	<tbody> </tbody>
Chlorella Powder Organic	100g <tbody> </tbody>

<tbody>
</tbody>

The first row shows how it is at the moment the second row shows how I would like it to be.

I hope there's a formula.
Thanks

 

[bjoern]

As long as there is one number only in your text, you could do something like: =SUM(MID(0&A3,LARGE(ISNUMBER(0+MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1))*ROW(INDIRECT("1:"&LEN(A3))),ROW(INDIRECT("1:"&LEN(A3))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A3)))/10)
I've put together a quick example sheet: https://spreadgit.com/bjoern/extract-number-in-text.xlsx/sheets/Sheet1

 

[Rick Rothstein]

I am assuming there is a better formula available, but until it is posted, try this...

Assuming your example text is in cell A1, put these formulas in the indicated cells...

B1: =SUBSTITUTE(A1," "&C1,"")

C1: =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),FIND(" ",A1&" ",MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))

 

[mfexcel]

@Rick, really like you trick in combining Numbers and " " to the original text. Brilliant!!!

Although not a better alternative, here's my trial.

B1
=SUBSTITUTE(A1,C1&" ","")

C1
=MID(A1,MIN(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),ROW(INDIRECT("1:"&LEN(A1))))),MAX(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),ROW(INDIRECT("1:"&LEN(A1)))))-MIN(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),ROW(INDIRECT("1:"&LEN(A1)))))+2)
CTRL SHIFT ENTER

 

[Rick Rothstein]

I am assuming there is a better formula available, but until it is posted, try this...

Assuming your example text is in cell A1, put these formulas in the indicated cells...

B1: =SUBSTITUTE(A1," "&C1,"")

C1: =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),FIND(" ",A1&" ",MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))

I think this might be the better solution...

B1: =SUBSTITUTE(A1," "&C1,"")

C1: =TRIM(MID(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),500)," ",REPT(" ",500)),1,500))

 

[dcecilia]

I am assuming there is a better formula available, but until it is posted, try this...

Assuming your example text is in cell A1, put these formulas in the indicated cells...

B1: =SUBSTITUTE(A1," "&C1,"")

C1: =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),FIND(" ",A1&" ",MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))

you are amazing.. I can't believe it.
Can you explain me the logic if i'm not asking too much?

 

[Rick Rothstein]

Do not use that formula, rather, use the formula I posted in Message #5... it should be more efficient.

As for explaining the formula, this part calculates the location of the first digit in the text...

MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))