CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Simon GIS
Simon GIS

Reputation: 1055

Regex Match Return all Numbers

I have this data in a Google Sheets Column

A2: BOX OF BOTTLES 
A3: BOX OF 10 BOTTLES 
A4: BOX OF 20 BOTTLES 
A5: BOX OF 30 BOTTLES

I would like my formula REGEXMATCH to return in this format :

B2 : BX-BT
B3 : BX-10BT
B4 : BX-20BT
B5 : BX-30BT

Thats my first try :

=IFS(REGEXMATCH(A2,"BOX OF BOTTLES"),"BX-BT". So its not working now if i have numbers like in A3,A4 and A5

Is there a way to make it work in Google Sheets?

Upvotes: 1

Views: 117

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522294

Using REGEXREPLACE we can try:

=REGEXREPLACE(A1, "BOX OF (?:(\d+) )?BOTTLES", "BX-$1BT")

Demo

Edit:

From your comments below, it seems that the above might not be working for you. This could be, going by your question, because you have trailing whitespace in your input text. Try accounting for that in the regex pattern:

=REGEXREPLACE(A1, "BOX OF (?:(\d+) )?BOTTLES\s*", "BX-$1BT")

Upvotes: 3

Related Questions