Cell got number with decimal and text too - How to separate

[DIPASGL]

I have got column like below that has got L (liter), kg(kilogram) and so on. If I want to separate after highphyne - sign but also just want number including decimal (not with kg or g etc). How can I do . I want to achieve 250 , 2.6, .45 , .250 .Below is the problem

GREASE - 250L

GREASE STRO 30 - 2.6KG

GREASE - .45KG

GREASE - 250G

 

[DIPASGL]

Thanks for reply but wt is 65:90 in below formula and how should I use this in my sheet

=0+LEFT(RIGHT(A2,LEN(A2)-FIND("- ",A2)-1),AGGREGATE(15,6,SEARCH(CHAR(ROW($65:$90)),RIGHT(A2,LEN(A2)-FIND("- ",A2)-1)),1)-1)

Thanks team but can I ask about below as above did not working on my excel. How to just remove text from numbers as below

450G

20L

1000kg

1000kiograms

20L

1L

200L

 

[Fluff]

Can you please confirm what version of Excel you are using?

 

[KRice]

Hopefully you've seen Peter's solution, which is designed to work on your older version of Excel. Mine still will not work on versions prior to Excel 2010...sorry about that. The 65:90 construction used in the ROW function creates an array of {65;66;67;...;90}, which are the ASCII character codes for the capital letters A-Z. The CHAR function used the numeric array to return the array of capital letters {"A";"B";...;"Z"}, and this was used by the SEARCH function to determine where the units abbreviation began in the text string. But I see now that you have some units with lower case letters, so this would need further adjustment.

Please have a look at Peter's offering instead.

 

[Sufiyan97]

But I see now that you have some units with lower case letters, so this would need further adjustment.

SEARCH function is case insensitive