CODEWITHSUNDEEP
excelexcel-formula
San Backups
San Backups

Reputation: 503

Remove letters from cells

I have a row of cells containing both text and numbers. I would like to AUTOMATICALLY create a row beneath it containing only the numbers and decimal point.

enter image description here

Is this possible with Office 2013?

Upvotes: 0

Views: 103

Answers (2)

pnuts
pnuts

Reputation: 59440

Try: 

=LEFT(MID(A1,FIND(" ",A1)+1,LEN(A1)),LEN(MID(A1,FIND(" ",A1)+1,LEN(A1)))-1)

An alternative to cover mg and mmg as well as g: 

=LEFT(MID(A1,FIND(" ",A1)+1,LEN(A1)),FIND(" ",MID(A1,FIND(" ",A1)+1,LEN(A1))))

Preserves the decimal point in Orange 1.0 g but does so by leaving the result as text format.

Upvotes: 1

Excel Hero
Excel Hero

Reputation: 14764

Try this in cell A2 (assuming first value is in cell A1):

=VALUE(MID(A1,FIND(" ",A1)+1,FIND("|",SUBSTITUTE(A1," ","|",2))-(FIND(" ",A1)+1)))

Now copy over.

Upvotes: 2

Related Questions