CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formula
sigur7
sigur7

Reputation: 936

Extract £ or % value from Google Sheets with REGEXTRACT

I have a Google Sheets formula that extracts a £ currency value or a percentage discount from a block of text.

=REGEXEXTRACT(B2,"[\d,.£%]+") - Extracts £ value or % discount (but other numbers too)

=REGEXEXTRACT(B2,"[\d,.]+") - Extracts digits, commas, or periods

However, if the text contains any others numbers before the £ value or % discount they get extracted first.

How can I only extract the £ value or % discount from each cell in Google Sheets?

The maximum discount displayed is 2 decimal places maximum, which may help in building a formula to extract 4 digits left or right of the value.

EXAMPLE DATA

Amy Wills 44% Discount
1Direction Food 45.37% Discount
AllUnder20 £120 Commission
AATU 13.31% Discount
Tickets4You £70 Commission
AllAboutU £7 Commission
Andrea Cardini 4% Discount

Upvotes: 1

Views: 587

Answers (2)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627326

You can use

=JOIN("", REGEXEXTRACT(B2, "£(\d+(?:[.,]\d+)?)|(\d+(?:[.,]\d+)?)%"))

Details:

  • £(\d+(?:[.,]\d+)?) - matches a £ and then matches and captures into Group 1 one or more digits followed with one or zero occurrences of ./, and then one or more digits
  • | - or
  • (\d+(?:[.,]\d+)?)% - matches and captures into Group 2 one or more digits followed with one or zero occurrences of ./, and then one or more digits, and then a % is matched.

See the demo screenshot:

enter image description here

See the RE2 regex demo.

Upvotes: 2

MattKing
MattKing

Reputation: 7783

Based on your samples, this should work.

=SUMPRODUCT(N(SPLIT(B2," ")))

You can see it at work here in cell C2.

Upvotes: 0

Related Questions