REGEX Google Sheets Conditional

Question

I have this formula in Google sheets.

=REGEXREPLACE(A3, "BOX OF\s*(\d+)?\s*BOTTLE", "BX-$1BT")

Original                          Expected
BOX OF 3 BOTTLE @ 10 TABLET       BX-3BT @ 10 TABLET
BOX OF BOTTLE @ 10 TABLET         BX-BT @ 10 TABLET
BOX OF 2 BLISTER @ 14 TABLET      BX-2BL @ 14 TABLET
BOX OF VIAL 5 ML                  BX-VL 5ML

I would like the formula to work for BOTTLE, BLISTER, etc...

Is that feasable?

You could use an alternation BOX OF\s*(\d*)\s*(?:(B)O(T)TLE|(BL)ISTER|(V)IA(L)) regex101.com/r/jz83gP/1 and use the capturing groups in the replacement. — The fourth bird
– The fourth bird, Commented Jun 23, 2020 at 9:50

The fourth bird · Accepted Answer · 2020-06-23 09:55:37Z

2

To get those replacement, one option is to use an alternation with capturing groups for the letters of the words and use the capturing groups in the replacement.

BOX OF\s*(\d*)\s*(?:(B)O(T)TLE|(BL)ISTER|(V)IA(L))

Regex demo

=REGEXREPLACE(A3, "BOX OF\s*(\d*)\s*(?:(B)O(T)TLE|(BL)ISTER|(V)IA(L))", "BX-$1$2$3$4$5$6")

answered Jun 23, 2020 at 9:55

The fourth bird

165k16 gold badges61 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Simon GIS Over a year ago

IF i complicate the thing, and add to your demo "BOTTLE OF 100 CAPLET" to Get "BT 100 CAPLET"

The fourth bird Over a year ago

The replacement is a bit different. I would use a simpler replacement for that part like match BOTTLE OF and replace with BT I am not sure if you can use a conditional replacement.

Collectives™ on Stack Overflow

REGEX Google Sheets Conditional

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related