Suiga tele tusitusiga i fua fa'atatau

Suppose you have a list in which, with varying degrees of “straightforwardness,” initial data is written – for example, addresses or company names:

It is clearly seen that the same city or company is present here in motley variants, which, obviously, will create a lot of problems when working with these tables in the future. And if you think a little, you can find a lot of examples of similar tasks from other areas.

Now imagine that such crooked data comes to you regularly, i.e. this is not a one-time “manually fix it, forget it” story, but a problem on a regular basis and in a large number of cells.

What to do? Do not manually replace the crooked text 100500 times with the correct one through the “Find and Replace” box or by clicking ctrl+H?

The first thing that comes to mind in such a situation is to make a mass replacement according to a pre-compiled reference book of matching incorrect and correct options – like this:

Unfortunately, with the obvious prevalence of such a task, Microsoft Excel does not have simple built-in methods for solving it. To begin with, let’s figure out how to do this with formulas, without involving “heavy artillery” in the form of macros in VBA or Power Query.

Case 1. Bulk full replacement

Let’s start with a relatively simple case – a situation where you need to replace the old crooked text with a new one. atoatoa.

Let’s say we have two tables:

In the first – the original variegated names of companies. In the second – a reference book of correspondence. If we find in the name of the company in the first table any word from the column Ina ia maua, then you need to completely replace this crooked name with the correct one – from the column Suia second lookup table.

Mo le faigofie:

• Both tables are converted to dynamic (“smart”) using a keyboard shortcut ctrl+T po o le au Insert – Table (Insert — Table).
  
• On the tab that appears Fausia (Mamanu) first table named Faʻamatalaga, and the second reference table – Faʻavae.

To explain the logic of the formula, let’s go a little from afar.

Taking the first company from cell A2 as an example and temporarily forgetting about the rest of the companies, let’s try to determine which option from the column Ina ia maua meets there. To do this, select any empty cell in the free part of the sheet and enter the function there IA MAUA (SU'E):

This function determines if the given substring is included (the first argument is all values ​​from the column Ina ia maua) into the source text (the first company from the data table) and should output either the ordinal number of the character from which the text was found, or an error if the substring was not found.

The trick here is that since we specified not one, but several values ​​as the first argument, this function will also return as a result not one value, but an array of 3 elements. If you do not have the latest version of Office 365 that supports dynamic arrays, then after entering this formula and clicking on ulu you will see this array right on the sheet:

If you have previous versions of Excel, then after clicking on ulu we will only see the first value from the result array, i.e. error #VALUE! (#VALUE!).

You shouldn’t be afraid 🙂 In fact, our formula works and you can still see the entire array of results if you select the entered function in the formula bar and press the key F9(just don’t forget to press Escto go back to the formula):

The resulting array of results means that in the original crooked company name (GK Morozko OAO) of all values ​​in a column Ina ia maua found only the second (Morozko), and starting from the 4th character in a row.

Now let’s add a function to our formula VIEW(VAA ANE):

O lenei galuega e tolu mafuaaga:

1. Taua mana'omia – you can use any sufficiently large number (the main thing is that it exceeds the length of any text in the source data)
   
2. Viewed_vector – the range or array where we are looking for the desired value. Here is the previously introduced function IA MAUA, which returns an array {#VALUE!:4:#VALUE!}
   
3. Vector_taunuuga – the range from which we want to return the value if the desired value is found in the corresponding cell. Here are the correct names from the column Suia our reference table.
   

The main and non-obvious feature here is that the function VIEW if there is no exact match, always looks for the nearest smallest (previous) value. Therefore, by specifying any hefty number (for example, 9999) as the desired value, we will force VIEW find the cell with the nearest smallest number (4) in the array {#VALUE!:4:#VALUE!} and return the corresponding value from the result vector, i.e. correct company name from the column Suia.

The second nuance is that, technically, our formula is an array formula, because function IA MAUA returns as results not one, but an array of three values. But since the function VIEW supports arrays out of the box, then we do not have to enter this formula as a classic array formula – using a keyboard shortcut ctrl+gāoioiga+ulu. A simple one will suffice ulu.

That’s all. Hope you get the logic.

It remains to transfer the finished formula to the first cell B2 of the column tumau – and our task is solved!

Of course, with ordinary (not smart) tables, this formula also works great (just don’t forget about the key F4 and fixing the relevant links):

Case 2. Bulk partial replacement

This case is a little trickier. Again we have two “smart” tables:

The first table with crookedly written addresses that needs to be corrected (I called it Faʻamatalaga2). The second table is a reference book, according to which you need to make a partial replacement of a substring inside the address (I called this table Substitutions2).

The fundamental difference here is that you need to replace only a fragment of the original data – for example, the first address has an incorrect “St. Petersburg” i le taumatau “St. Petersburg”, leaving the rest of the address (zip code, street, house) as is.

The finished formula will look like this (for ease of perception, I divided it into how many lines using Alt+ulu):

The main work here is done by the standard Excel text function SUI FUA (SUFITU), which has 3 arguments:

1. Source text – the first crooked address from the Address column
2. What we are looking for – here we use the trick with the function VIEW (VAA ANE)from the previous way to pull the value from the column Ina ia maua, which is included as a fragment in a curved address.
3. What to replace with – in the same way we find the correct value corresponding to it from the column Suia.

Enter this formula with ctrl+gāoioiga+ulu is not needed here either, although it is, in fact, an array formula.

And it is clearly seen (see #N/A errors in the previous picture) that such a formula, for all its elegance, has a couple of drawbacks:

• galuega tauave SUBSTITUTE is case sensitive, so “Spb” in the penultimate line was not found in the replacement table. To solve this problem, you can either use the function ZAMENIT (SUFI), or preliminarily bring both tables to the same register.
• If the text is initially correct or in it there is no fragment to replace (last line), then our formula throws an error. This moment can be neutralized by intercepting and replacing errors using the function IFERROR (IFERROR):

  

• If the original text contains several fragments from the directory at once, then our formula replaces only the last one (in the 8th line, Ligovsky «Avenue« suia i “pr-t”, Ae “S-Pb” on “St. Petersburg” no longer, because “S-Pb” is higher in the directory). This problem can be solved by re-running our own formula, but already along the column tumau:

  
  

Not perfect and cumbersome in places, but much better than the same manual replacement, right? 🙂

PS

In the next article, we will figure out how to implement such a bulk substitution using macros and Power Query.

• How the SUBSTITUTE function works to replace text
  
• Finding Exact Text Matches Using the EXACT Function
• Case sensitive search and substitution (case sensitive VLOOKUP)