Search code examples
google-sheetsgoogle-sheets-formulaextract

How to extract number from text in Google Sheets


My product names contain different sizes: e.g. 100ml, 50ml, 30ml.

Sample product name:

Babaria Aloe Vera Shaving Gel Sensitive Skin 200ml

From this I try extract capacity: 200ml.

To make it easier to do this, I am sharing a document.

https://drive.google.com/file/d/1gPuNiNwvK1bG5WxD-4kFgrYjSF72SkwX/view?usp=sharing

Could somebody help me and give me some example?

If it's hard to extract numbers with "ml", it also might be helpful for me to extract only numbers.


Solution

  • You can use:

    =INDEX(IF(A2:A="","",REGEXEXTRACT(A2:A,"\b\d+ml\b")))
    

    Where \b\d+ml\b means:

    • \b - A word-boundary to assert position has no leading word-characters.
    • \d+ml - 1+ Digits followed by literally "ml".
    • \b - A word-boundary to assert position has no trailing word-characters.

    If you just want the numbers without "ml" then try to change the pattern to \b(\d+)ml\b where the only difference is the use of a capture group which will be the result of the REGEXEXTRACT() function here.