Extracting a number from a string

%3CLINGO-SUB%20id%3D%22lingo-sub-2258520%22%20slang%3D%22en-US%22%3EExtracting%20a%20number%20from%20a%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2258520%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20extract%20the%20number%20from%20a%20string%20such%20as%20%22100%20ml%22%26nbsp%3B%20I've%20got%20a%20cocktail%20spreadsheet%20with%20the%20ingredients%20listed%20as%20volumes%20such%20as%20%225%20ml%22%2C%2012.5%20ml%22%20or%20%22100%20ml%22%20and%20I%20want%20to%20be%20able%20to%20use%20the%20number%20to%20calculate%20the%20cost%20using%20a%20look%20up%20table.%26nbsp%3B%20I'm%20OK%20with%20LUTs%20but%20don't%20know%20how%20to%20get%20the%20number%20out%20of%20the%20string%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2258520%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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

@MikeRSA

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 

@MikeRSA 

As variant

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