Find and replace multiple occurrences of text simultaneously using Array Formulas (Advanced)

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

In the Topic Example Workbook we have in the first column a list of Salespeople with full titles (Mr, Mrs, Miss etc) and in columns F and G we have a list of titles with corresponding gender. What we would like to do is replace any male Title with “MALE-“ and any female title with ‘FEMALE-’. So we want to look up any occurrences of a title within each cell within Column A and replace it with the corresponding gender.

Without the use of VBA or an array formula, we would have to lookup each title manually and could replace any instances using the SUBSTITUTE function to replace all columns, all of which would become very messy with a large range of values to lookup and replace.

The method we will look at here is using an array formula – before continuing it is important to have an understanding of array formulas which you can do by reading the topic Creating your own MAXIF/MINIF formula using an array formula

The methodology for following this is firstly to build an array formula which will look through all the titles (in the range F2:F6) and if its finds a match it will return the row number In that range where it occurs. We can then use the SUBSTITUTE function and [INDEX/MATCH] functions to replace the title with the corresponding value (in the same row) in the range G2:G6

The array formula we need in cell B2 (looking up any occurrence of a title in Column A) is {=MATCH(1,SEARCH(“*”&$F$2:$F$6&”*”,A2))}, entered using Ctrl + Shift + Enter to use it as an array formula.

Breaking down this formula into its constituent parts:

• “*”&$F$2:$F$6&”*”: This is using the wildcard lookup “*” to indicate any text – so the formula is taking each value in the range F2 to F6 in turn and putting it as part of a text string with a wildcard either side (which can then be looked up in the cell value in cell A2). Note that because of the use of wildcards, if we have any text values which contain other text values (like we have here – the text ‘Mr’ is contained in the text ‘Mrs’) the longer text string (Mrs) must be listed first
• SEARCH(“*”&$F$2:$F$6&”*”,A2): The SEARCH function looks for some text within a text value/cell and returns the character on which it occurs if it can be found. So this is looking for each value in F2:F6 being contained in the value in cell A2
• MATCH(1,SEARCH(“*”&$F$2:$F$6&”*”,A7)): The MATCH function looks for a value in a column range and returns the row number on which it occurs, or looks in a row range and returns the column number in which it occurs. So this is looking within the range F2:F6 for the text referred to above, and if a match is found the row number (within the range F2:F6) will be returned.

This formula then returns the row number of the matching title within the range of titles (F2:F6). For example the formula applied to cell A2 (“Mr Andrew Phillips”) returns 5 because the matching title (“Mr”) occurs in the 5th row of the range F2:F6 (i.e. in cell F6).

Once we have the required row number we can then use the INDEX function to return the corresponding replacement value (e.g. in Column G). So looking in Column G in row number 5 returns “MALE-” which is what we want to substitute the “Mr” text in cell A2 in. For this we can use the SUBSTITUTE function. If the row number is in cell B2 then we can use this in the SUBSITUTE function in cell C2 which references the full name in cell A2 (“Mr Andrew Phillips”), substituting any occurrences of the text “Mr” with “MALE-“. The completed formula is =SUBSTITUTE(A2,INDEX($F$2:$F$6,B2,1),INDEX($G$2:$G$6,B2,1)&”-“).

In the Topic Example Workbook, carry out the steps described above by replacing any instances of Male titles with ‘MALE-‘ and Female with ‘FEMALE-‘. The list of titles is shown in the table to the right

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.