Let's say you have a list of countries in column A and aim to replace all the abbreviations with the corresponding full names. In situation when you are looking to replace the entire cell content, not its part, the XLOOKUP function comes in handy. The SUBSTITUTE function is case-sensitive, meaning you should type the old values ( old_text) in the same letter case as they appear in the original data.Īs easy as it could possibly be, this method has a significant drawback - when you have dozens of items to replace, nested functions become quite difficult to manage.Īdvantages: easy-to-implement supported in all Excel versionsĭrawbacks: best to be used for a limited number of find/replace values Search and replace multiple entries with XLOOKUP Please pay attention that, in this case, we lock the replacement values with absolute cell references, so they won't shift when copying the formula down. In pre-dynamic versions of Excel 2019, Excel 2016 and earlier, the formula needs to be written for the topmost cell (B2), and then copied to the below cells: Please note, the above approach only works in Excel 365 that supports dynamic arrays. …and you will have all the replacements done at once: And then, put the below formula in B2 and press Enter: To have it done, enter the old values in D2:D4 and the new values in E2:E4 like shown in the screenshot below. In the list of locations in A2:A10, suppose you want to replace the abbreviated country names (such as FR, UK and USA) with full names. SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( text, old_text1, new_text1), old_text2, new_text2), old_text3, new_text3) And then, you nest those functions one into another, so that each subsequent SUBSTITUTE uses the output of the previous SUBSTITUTE to look for the next value. The formula's logic is very simple: you write a few individual functions to replace an old value with a new one. The easiest way to find and replace multiple entries in Excel is by using the SUBSTITUTE function. Multiple find and replace using Substring toolsįind and replace multiple values with nested SUBSTITUTE.Bulk-replace macro to find and replace multiple values.Multi-replace using recursive LAMBDA function.XLOOKUP formula to search for multiple words.Nested SUBSTITUTE formula to replace multiple values.Luckily, there are a few more effective methods to do mass replace in Excel, and we are going to investigate each of them in detail. But what if you have tens or even hundreds of items to replace? Surely, no one would want to make all those replacements manually one-by-one, and then do it all over again when the data changes. How do people usually search in Excel? Mostly, by using the Find & Replace feature, which works fine for single values.
In this tutorial, we will look at several ways to find and replace multiple words, strings, or individual characters, so you can choose the one that best suits your needs.
0 kommentar(er)
