CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaextract
Tom Arm
Tom Arm

Reputation: 143

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.

Upvotes: 0

Views: 227

Answers (1)

JvdV
JvdV

Reputation: 75840

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.

Upvotes: 4

Related Questions