Great trick! UPDATE: Hey, I managed to do the replacement using Excel formulas SUBSTITUTE and VLOOKUP. Here's how: (I'm using the example at 1:02). At cell L4, type =IFERROR(SUBSTITUTE(K4,VLOOKUP(K4,N:O,1,FALSE),VLOOKUP(K4,N:O,2,FALSE)),K4) and then copy the formula down the table and you'll have it.
I Started with absolutely NO knowledge of what a VBA is...Once I figured that out and then how to find the developers tab in excel, I followed the directions and IT WORKED! So simple. THANK YOU!!!!!
Thank you!!! This was beyond helpful for a project I am working on for work right now. Over a thousand customer numbers need to be updated to new numbers in one spreadsheet and my client is only going to give me a different spreadsheet with the old number and new number. Now I can simply add their two columns to my spreadsheet and use this method to update the original customer number column!
Thanks so much, the code listed in other websites did not work for me for some reason, but yours did! Took several hours of trial and error before I came across your video
I found a workaround if you would like to change the formatting(colour/bold ex). For your key use the original data(eg. 154 /style you want to replace in the first column and in the second column use the concatenate function to add a unique suffix or prefix (xx154xx). After running the script use conditional formatting to edit the data containing your suffix (xx).
You, my friend, are a genius. THANK YOU. Worked like a charm. Can't tell you how much time this will save me moving forward with a work responsibility!
sir i want to say a huge thank you from the bottom of my heart, you just saved my life😭 i made a mistake coding a data on my google form questionaire and i have to change it all to the correct ones. I was hopeless because i have 291 respondents but with this video i can solve it in less than a minute! I owe you lots, thank you once again😭🙏 btw you deserve more subs. i just subscribed
Excellent Idea. This is what I was exactly looking for. Great handy video. It saves a lot of time especially when you have a great bunch of excel sheets.
Really insightful video. Howeved, for the second case involving numbers, can't we just do a simple lookup. And for the first case, we can use a combination of REDUCE, SUBSTITUTE and OFFSET I believe. Nevertheless, great tutorial. Thanks for sharing!
Hi there, this worked perfectly first time, but I had to save my excel as a macro enabled one first. I did this on an excel saved on teams, which is linked to a MS form (where the data is coming from). 2 questions, how do I keep the MS form linked to my new 'macro enabled' excel, and how do you make the formula/macro automatic, or do you have to keep re-running it? I am trying to automate the answers I receive from my MS form, so that strings of text are automatically replaced by numbers (my key). thanks.
Will this work on items of text within a wider range of text and cells? Ie change bananas to oranges even if an individual cell has say 'bananas are great' so that it changes to oranges are great?
Wow! I hope this will do the trick. Maybe you can answer that for me. Will this find and replace all values if the select range is Only 1 cell with a Wall of text? Specifically a Wall of Html code, and I want to find and replace values acording to the key?
This is great! Thank you! How would you change the code to not match the entire cell contents? Each of your examples were single words in the cell. I am trying to replace names within full sentences. Thanks again!
Does this work on 1 sheet (it seems this way since you select the range), or through the entire workbook? If it is only on 1 sheet, is there a way to make it through the entire workbook?
Is there a way to do the same thing when there are multiple items within a single cell that should be replaced? In your first example if you started in a cell that contained "Apples, Bananas" and you wanted to change it to "Algerian Apples, Mexican Bananas".Thanks very much for the great video!
You have saved me so much time in my workflow!!!! Thank you! I do have 1 question, is it possible to do this to partial text? Such as "Mrs Smith" to change to "Ms Smith" while having 1000 different names? Thanks!
Wow! works fine, but is there a way to make it search for a particular keyword in a sentence and replace, like "I am Simeon" is part of the list and I want to replace just Simeon from that sentence. thanks
This is cool and you obviously know what you're doing, I think the issue I have is that I don't know how you put together the VBAS code, and therefore I don't feel like I learned anything here. Plus if you're going to set up the file the way you did with a key, then why not use an Xlookup to change the values? I bet that would be even quicker than the solution you provided. Again, not disrespecting your work, just didn't feel it had any value for me. Thanks.
It's only changing the cell that match exactly the case? Can you please adjust the code so it can change any letter or word in a cell that match the citeria? My case when a cell has multiple value like this Jack, John, Jeremy, Jane the code just skip this cell
No he probably can't as he didn't write the code as it is copied from a company called ExtendOffice.com that makes an Excel add-in called KuTools that he references at the end of the video, you can get the code and some more info on mulit find and replace at their link here: www.extendoffice.com/documents/excel/1873-excel-find-and-replace-multiple-values-at-once.html Nice video on how to use the code.
It works perfectly. The input boxes that pop up don't specify the question, so I found that I had to blindly select the original values first, click ok, select the replacement values next, and click ok. Perhaps this might help those who found that it doesn't work?
Nice if you want to replace full values, however partially isn't possible. E.g: people fill in 1:30 or 1:45 or 1:15 for time. I would like to replace :30 with .5 and :45 with .75 and :15 with .25. This script won't allow this, maybe with some minor changes?
I hit run and I get a Compile error: Syntax error For Each Rng In ReplaceRng.Columns(1).Cells InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, Lookat:=xlWhole
any idea why I get a syntax error for this line?For Each Rng In ReplaceRng.Columns(1).Cells InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, Lookat:=xlWhole
I managed to do it with Excel formulas only. Using the example at 1:02 , type at Cell L4 =IFERROR(SUBSTITUTE(K4,VLOOKUP(K4,N:O,1,FALSE),VLOOKUP(K4,N:O,2,FALSE)),K4) and then copy the formula down the entire table.
🙏Thanks for all the feedback! Honored!
Please note I don't offer any troubleshooting beyond what the video illustrates.
life savior (y)
How to color replaced cells following replacement
Great trick!
UPDATE: Hey, I managed to do the replacement using Excel formulas SUBSTITUTE and VLOOKUP.
Here's how: (I'm using the example at 1:02). At cell L4, type =IFERROR(SUBSTITUTE(K4,VLOOKUP(K4,N:O,1,FALSE),VLOOKUP(K4,N:O,2,FALSE)),K4) and then copy the formula down the table and you'll have it.
That's pretty slick! worked perfectly. But Can you explain what is going on in the Formula here - (K4,N:O,1,FALSE) - what are the 1 and the FALSE?
Excellent.
You, sir, are my hero for the day! Thank you very much!
Thank you SOOOOO much. I couldn't figure out how to get he macros to work on my mac but this formula is fool proof!
You're a genius. Thanks
This was big time help, makes look like a genius =) THANK YOU 7yrs later
3 MONTHS! You turned 3 MONTHS of agonizing pain into a 4:56 min video, I don't know you, but you are a GOD!
I Started with absolutely NO knowledge of what a VBA is...Once I figured that out and then how to find the developers tab in excel, I followed the directions and IT WORKED! So simple. THANK YOU!!!!!
Thank you!!! This was beyond helpful for a project I am working on for work right now. Over a thousand customer numbers need to be updated to new numbers in one spreadsheet and my client is only going to give me a different spreadsheet with the old number and new number. Now I can simply add their two columns to my spreadsheet and use this method to update the original customer number column!
Glad it was helpful!
Thank you for this dude. You saved me a week worth of work
Wow! Glad we crossed paths!
One of the most useful tips for Excel!!
Thanks Sandy, I’m glad it was useful!
Thanks so much, the code listed in other websites did not work for me for some reason, but yours did! Took several hours of trial and error before I came across your video
this video saves my life so often. my notes on mac: Open visual basic in excel > developer tab
Insert model is the button that looks like a dna
used this 4 times now in the last 10 days
and again
Again!
I found a workaround if you would like to change the formatting(colour/bold ex). For your key use the original data(eg. 154 /style you want to replace in the first column and in the second column use the concatenate function to add a unique suffix or prefix (xx154xx). After running the script use conditional formatting to edit the data containing your suffix (xx).
BRILLIANT!! I've been looking for this for days and you set it up so simply
Cannot thank you enough... I barely like videos and commenting for me is way too exceptional, Thanks A TON!!
Thank you so much its very helpful after spending hours and hours to find a solution for replacing values
You’re welcome! Glad you found help for your project.
Works great. Tip: make a copy of your original column so you don't lose those values.
Very useful. I learned from your videos. Thanks for the information sir.
Worked perfectly for me! Used this 'key' method to replace several texts across multiple columns. Using Excel 2016.
This worked like a charm and saved me so much time. THANK YOU!
Thanks Handiman... Works like a dream. You're a machine
Contents is appreciated for common users. Keep continuing....
Thanks, will do!
thanks man really helped me
You, my friend, are a genius. THANK YOU. Worked like a charm. Can't tell you how much time this will save me moving forward with a work responsibility!
thanks a lot you saved my life it worked like magic on 200k rows :)
sir i want to say a huge thank you from the bottom of my heart, you just saved my life😭 i made a mistake coding a data on my google form questionaire and i have to change it all to the correct ones. I was hopeless because i have 291 respondents but with this video i can solve it in less than a minute! I owe you lots, thank you once again😭🙏
btw you deserve more subs. i just subscribed
Wow... I'm glad you found this video, @qisthiaryani6612. Thanks for the feedback!
Excellent Idea. This is what I was exactly looking for. Great handy video. It saves a lot of time especially when you have a great bunch of excel sheets.
Glad it was helpful!
Really insightful video. Howeved, for the second case involving numbers, can't we just do a simple lookup. And for the first case, we can use a combination of REDUCE, SUBSTITUTE and OFFSET I believe. Nevertheless, great tutorial. Thanks for sharing!
Thanks. It still works in 2022. As a few people have mentioned, keep a copy of the original data as undo/cntrlZ would not undo the replacements.
Thank you for this... Indeed, no University like TH-cam.
Glad it was helpful!
Hi there, this worked perfectly first time, but I had to save my excel as a macro enabled one first. I did this on an excel saved on teams, which is linked to a MS form (where the data is coming from). 2 questions, how do I keep the MS form linked to my new 'macro enabled' excel, and how do you make the formula/macro automatic, or do you have to keep re-running it? I am trying to automate the answers I receive from my MS form, so that strings of text are automatically replaced by numbers (my key). thanks.
I don't know who you are but god bless you, sir.
Worked like a charm and is very flexible. Thx a lot!
Bruh, you're a gem! Thanks a lot for this incredibly cool tip. Saved a lot of time for me.
is the kutools needed for this?
Will this work on items of text within a wider range of text and cells? Ie change bananas to oranges even if an individual cell has say 'bananas are great' so that it changes to oranges are great?
Truly, handyman, this was a blessing!!!!
Excellent procedure and helpful when you have huge data to replace. Thank you.
its works like awesome.. is there any parameter for case ?
BIG THANKS, YOU'VE SAVED MY LIFE !
You saved ton of my time buddy. Thank you so much and subscribed.
This is fantastic, great work. Works so well on anything that I have used it on. Thank you.
Wow! I hope this will do the trick. Maybe you can answer that for me. Will this find and replace all values if the select range is Only 1 cell with a Wall of text? Specifically a Wall of Html code, and I want to find and replace values acording to the key?
This is great! Thank you! How would you change the code to not match the entire cell contents? Each of your examples were single words in the cell. I am trying to replace names within full sentences. Thanks again!
@yorkebt1 posted below to change "Lookat:=xlWhole" to "Lookat:=xlPart". That worked for me...
Thanks for the help... saved me loads of time...
Thanks Ryan!
Can this be used to change the format of the cell as well?
Hi is there a way to do this for styling only ?
Dude are you a magican? excel wizard!
Worked for me! Many thanks!
This works and is excellent. Saves a lot of time and efforts. Thank you for this share
Anyway I could do it on google sheets? Thanks!
Is there a way to make this run with cells that have a character length over 255?
Does this work on 1 sheet (it seems this way since you select the range), or through the entire workbook? If it is only on 1 sheet, is there a way to make it through the entire workbook?
This is just awesome and will safe me a lot of time. Thanks a lot!
Is there a way to do the same thing when there are multiple items within a single cell that should be replaced? In your first example if you started in a cell that contained "Apples, Bananas" and you wanted to change it to "Algerian Apples, Mexican Bananas".Thanks very much for the great video!
In VBA, try replacing "Lookat:=xlWhole" (at the bottom on the right-hand side) by "Lookat:=xlPart"
@@marcelmansfeld7287 Thanks so much! This worked perfectly for me!
This saved my life....Thanks a Ton . Do you have any official website of yours. Would love to join in there
i have to replace multiple text in visio as per excel data please suggest for this
Can we do the same but without using visual basic
Most amazing functionality Thanks A TON
Glad it was helpful!
Thanks buddy.. it's very helpful.
Many many thanks!!! Great Job, it saved a lot of editing time
Glad it helped!
You are a life saver!!
This works great thanks :)
You have saved me so much time in my workflow!!!! Thank you! I do have 1 question, is it possible to do this to partial text? Such as "Mrs Smith" to change to "Ms Smith" while having 1000 different names? Thanks!
Did you ever find an answer to this question?
This code is not working with replacing partial text, did u find one?
In VBA, try replacing "Lookat:=xlWhole" (at the bottom on the right-hand side) by "Lookat:=xlPart"
@@marcelmansfeld7287 Can confirm this works. Thanks dude!
Thank you so much!! Saved me so much time! :)
Your learning is very good
Thanks it worked
Wow! works fine, but is there a way to make it search for a particular keyword in a sentence and replace, like "I am Simeon" is part of the list and I want to replace just Simeon from that sentence. thanks
Do you find an answer to your question? I am looking for the same.
@@arvisingh6613 Same. :(
@@arvisingh6613 Change "Lookat:=xlWhole" to "Lookat:=xlPart". That worked for me.
Change "Lookat:=xlWhole" to "Lookat:=xlPart". That worked for me.
Amazingly easy and effective !!! Thank you much for sharing :)
Thanks alot man. you saved my life
Glad to hear that!
AWESOME!!! Thanks for sharing. Very useful. Subscribed now.
Thanks for the sub!
Awesome help, Thank you for sharing
Glad it was helpful!
You are amazing. Thank you Ken
This is cool and you obviously know what you're doing, I think the issue I have is that I don't know how you put together the VBAS code, and therefore I don't feel like I learned anything here. Plus if you're going to set up the file the way you did with a key, then why not use an Xlookup to change the values? I bet that would be even quicker than the solution you provided. Again, not disrespecting your work, just didn't feel it had any value for me. Thanks.
Ah, thanks for the feedback Daniel.
hvala puno. radi perfektno...thank you very much it work perfect
works only with specific cells not with the cell data merged with other containts.can you please help on it.
Thank you. you are my solution
This was Awesome!
Thank you very much!!! It is so perfect!!!
It replaces when "Match Entire Case" is true. Is it possible to replace without matching entire case?
When I hit F5, my keyboard light is turning on. I cannot open kutools for excel. What to do, please help!
Lifesaver...THANK YOU
really helpful you can trust it
Amazing, solved my problem...thanks a lot
Great video, It is very useful.
Can you automat this using Macros? I tried, but it didn't work.
How do you complete this for column C in the entire workbook with sheet names ranging from KB1 to KB300?
Wow. Thank you for this tutorial. A+
You're very welcome!
Worked out thank you 👍🏻
You're welcome!
why its replacing 23 with some like 43892 instead of what I want, which is 2-3?
It's only changing the cell that match exactly the case?
Can you please adjust the code so it can change any letter or word in a cell that match the citeria?
My case when a cell has multiple value like this Jack, John, Jeremy, Jane the code just skip this cell
No he probably can't as he didn't write the code as it is copied from a company called ExtendOffice.com that makes an Excel add-in called KuTools that he references at the end of the video, you can get the code and some more info on mulit find and replace at their link here: www.extendoffice.com/documents/excel/1873-excel-find-and-replace-multiple-values-at-once.html Nice video on how to use the code.
Change "Lookat:=xlWhole" to "Lookat:=xlPart". That worked for me.
How to replace with format only like the words colour?
Awesome AWeome. Thank you very much
BEST THING EVER!!!!
Thanks for the code, worked great!
thanks my dear , its working fine
Glad to hear that
This is a great time saver!!! Thank you
It worked!! Thanks so much:)
It works perfectly. The input boxes that pop up don't specify the question, so I found that I had to blindly select the original values first, click ok, select the replacement values next, and click ok. Perhaps this might help those who found that it doesn't work?
Nice if you want to replace full values, however partially isn't possible.
E.g: people fill in 1:30 or 1:45 or 1:15 for time.
I would like to replace :30 with .5 and :45 with .75 and :15 with .25.
This script won't allow this, maybe with some minor changes?
In VBA, replace "Lookat:=xlWhole" (at the bottom on the right-hand side) by "Lookat:=xlPart"
I hit run and I get a Compile error: Syntax error
For Each Rng In ReplaceRng.Columns(1).Cells InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, Lookat:=xlWhole
Me too
I am getting a runtime error 13
Please help
any idea why I get a syntax error for this line?For Each Rng In ReplaceRng.Columns(1).Cells InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, Lookat:=xlWhole
I managed to do it with Excel formulas only. Using the example at 1:02 , type at Cell L4 =IFERROR(SUBSTITUTE(K4,VLOOKUP(K4,N:O,1,FALSE),VLOOKUP(K4,N:O,2,FALSE)),K4) and then copy the formula down the entire table.