find & replace multiple different words at once

[Sorro911]

Hi,

Does anyone know how I can find & replace multiple different words at once in Excel

example:

dog, cat, bird, pig, cow, chicken

***replace with a single word***

fish

[#2]

https://prod.support.services.microsoft.com/en-us/office/regexreplace-function-9c030bb2-5e47-4efc-ba...

=regexreplace(A2,"dog|cat|bird|pig|cow|chicken","fish")

or
=WEBSERVICE("https://e.anyoupin.cn/eh3/?regreplace~dog|cat|bird|pig|cow|chicken~fish~" & A2)

[#3]

@peiyezhu 

Hi Mate, I'm still struggling with this. Am i on the right track?

[#4]

@peiyezhu 

This would work best if I can learn how to format it correctly. Also is there a way to make his not case sensitive? e.g. DoG|caT|BIRd|coW|CHICKEn   > fish

I really appreciate the help! Thanks in advance.

[#5]

The formular regexreplace only available for Office 365 Beta channel.
I guess your Excel should not support it.

Have you tried the webservice?

[#6]

I'm using Microsoft Office Professional Plus 2021
Could you explain more on the webservice and how that works?

[#7]

The syntax of the REGEXEXTRACT function is:

REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])

case_sensitivity

Determines whether the match is case-sensitive. By default, the match is case-sensitive. Enter one of the following:

0: Case sensitive

1: Case insensitive



=WEBSERVICE("https://e.anyoupin.cn/eh3/?regreplace~/dog|cat|bird|pig|cow|chicken/i_@//~fish_@~" & A2)

[#8]

Thank you very much for your time and help but non of this has helped.

[#9]

https://support.microsoft.com/en-us/office/webservice-function-0546a35a-ecc6-4739-aed7-c0b7ce1562c4

[#10]

@Sorro911 

This is little vague, but you can try this.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "dog", "fish"), "cat", "fish"), "bird", "fish"), "pig", "fish"), "cow", "fish"), "chicken", "fish")

[#11]

@Sorro911 

= LET(
    rgx, {"dog|cat|cow|duck|chicken"},
    REGEXREPLACE(text, rgx, "fish")
  )

[#12]

Hey mate what am i doing wrong?

[#13]

@PeterBartholomew1 

Hey mate i still think im doing this wrong

[#14]

I had an elegant recursive solution for you but realized I was using a few functions not available in Excel 2021. The function gap between 2021 and 365 gets larger by the month it seems.

[#15]

Hey mate send it through i can use 365 Web for free by the looks of it.

https://www.microsoft.com/en-au/microsoft-365/free-office-online-for-the-web

[#16]

I can also use google sheets if this helps

[#17]

@Sorro911 

It seems I have something working decently with Power Query:

• Could you share with i.e. OneDrive, Google drive (or the like) a workbook with a representative dataset?
• More importantly what does a word mean with regard to what you expect? For example:
  - We have value "A cow-boy and a cow at farm" & you want to replace cow with fish. Should cow-boy be replaced as fish-boy?
  - Same question as above with value "A cow_boy and a cow at farm"
  ...etc