Extracting a number from a string

New Contributor

I'm trying to extract the number from a string such as "100 ml"  I've got a cocktail spreadsheet with the ingredients listed as volumes such as "5 ml", 12.5 ml" or "100 ml" and I want to be able to use the number to calculate the cost using a look up table.  I'm OK with LUTs but don't know how to get the number out of the string

4 Replies


Let's say you have a string such as "12.5 ml" in cell A2.

In another cell, enter the formula


=--LEFT(A2,FIND(" ",A2)-1)


This can be filled down if required.

Thank you very much.  That's exactly what I need.

I would never have got that without help.

@Hans Vogelaar 


As variant

=SUBSTITUTE(A1," ml","")*1
you can use also text to columns in data tab & delimiter is space