CODEWITHSUNDEEP
regexgoogle-sheets
aiorr
aiorr

Reputation: 589

Adding hyphen between specified prefix and suffix

enter image description here

Suppose prefix={mr,ms} and suffix={junior,senior}.

I wish to add a hyphen after the specified prefix and before the specified suffix.

Individually, I can do something like =REGEXREPLACE(A1,"mr","mr-") and =REGEXREPLACE(A1,"senior","-senior"), but is there a way to do everything above with a single formula?

Upvotes: 0

Views: 96

Answers (1)

Peter Thoeny
Peter Thoeny

Reputation: 7616

You can use capture groups to simplify that. Here are two nested replaces, one for the ms and mr case, and one for the junior and senior case:

=REGEXREPLACE(REGEXREPLACE(A38,"^(mr|ms)","$1-"),"(junior|senior)$","-$1")

Explanation "^(mr|ms)":

  • ^ - start of string (this avoids false hits such as msmiriamreich)
  • (mr|ms) - capture group with ORed combination of all prefixes
  • reference capture group with $1

Explanation "(junior|senior)$":

  • (junior|senior) - capture group with ORed combination of all postfixes
  • $ - end of string
  • reference capture group with $1

Cramming the two regexes into one is possible, but you would get a rather long regex because you have to consider permutation of (with/without prefix) and (with/without postfix).

Upvotes: 2

Related Questions