Cool Excel Trick to Find Replace Multiple Words in one go 👌

Here is an awesome Excel trick to find & replace multiple words in one go. I used this recently to bulk rename our products easily.

Imagine you have a bunch of product names in the range C5:C15 and you want to find / replace different words. The list of words to find is in the range G5:G10 and the replacements are in the range F5:F10.
We can use the formula =REDUCE(C5, $G$5:$G$10, LAMBDA(a, b, SUBSTITUTE(a, b, offset(b,0,1))))
to bulk replace all the words with new ones!

NEED TO DO THIS IN POWER QUERY ?

ADVANCED EXCEL FORMULAS -

#advancedexcel

[wayneedmondson]

Great one Chandoo! You can write one formula and spill all the results by defining the initial value as the range C5:C15, as in: =REDUCE(C5:C15, G5:G10, LAMBDA(a, b, SUBSTITUTE(a, b, OFFSET(b, 0, 1)))). Thumbs up!!

[Himanshubhadauria-fw]

It takes only few minutes to appreciate this type of help from someone and we should do thankyou sir for your content

[Reduce_Scan]

For those who do not like OFFSET
=REDUCE(C5:C9, SEQUENCE(ROWS(G5:G10)),
LAMBDA(a, b, SUBSTITUTE(a, INDEX(G5:G10, b), INDEX(H5:H10, b))))

[venkateshathreya]

Hi Chandoo...I thought I was good at Excel but you have set a new baseline. It is fun to learn excel from you. Thanks for the wonderful tips and tricks. Wish you most and more

[ashwinikumar]

Actually I was looking for a formula to separate the numbers and alphabets from an alphanumeric. This solution idea was so ideal and simple. Thanks a lot. You are amazing

[Reduce_Scan]

=REDUCE(C5:C15, G5:G10, LAMBDA(a, b, SUBSTITUTE(a, b, OFFSET(b, 0, 1))))

[BhavyaGupta]

Great Tutorial 👏
This is my attempt in solving it without Offset -

=REDUCE(C5:C15, G5:G10, LAMBDA(x, y, SUBSTITUTE(x, y, XLOOKUP(y, G5:G10, H5:H10))))

[sandyfreborg]

This is awesome and exactly what I needed. Is there any way this could be done in PowerQuery/Power BI? I have several columns where this formula could be used (on different arrays) and PBI seems like a great way to get this done

[IronDino]

Thank you so much for this. Exactly what I needed. You've got my subscription.

[MrEagle]

I was working on a very similar problem trying to replace a list of sentences in a block of text and took the help of Scan function. To get the final value, used a combination of index and rows functions to retrieve last value in the array. 2 hours later it struck me that Reduce would've been the better option instead of that Frankensteinian formula I created 🤣

[GeertDelmulle]

Hey Chandoo,
Just for fun, here's a formula to do the entire array in one formula (single cell calculation – no need to fix the cell references on the transform table):
=MAP(C5:C10, LAMBDA(rng, REDUCE(rng, G5:G10, LAMBDA(a, b, SUBSTITUTE(a, b,
Cheers!

[hasannaomir.]

Thank you so very much, sir. Do you have Microsoft intro email (access), PowerPoint, Excel, Word, and the others? Would you provide a pictionary-type course of all the elementary and vital vocabulary? Oh yes!!! Please??? nicely introduce to class one note, Google sheets? One more thing, dear Professor: anything you can provide to help dinosaurs catch up, maintain, lose fear, advance and then thrive? Please help? You are so nice 😊

[owwoone]

Candoo... do you have a tutor video how to automativally change the colours of values number in Excel ..for example if it minus then the value will have a Red Colour ?

[mayankadhaduk]

Sir please make one video series on google sheets also. It will be very much helpful.

[thebhaskarjoshi]

Pls make more videos like this using Lambda.

[rise]

Hi can you tell me is there any job opportunities in Excel freelance now...?? Plz guide me

[sushilade]

Sir, Meri Excel file ko koi b copy paste na kre isliye kuch solution he?

[MrDushyant]

Pls make video on combine data without adding same values..

[saravananbaskar]

How can we acheive this functionality without LAMBDA and OFFSET?

[rajatmishra]

Sir I have enquired in a coaching institute to learn advance Excel and they are saying that they will make me learn advance Excel in 45 days.
Well is it possible to learn advance Excel in 45 days?