Search code examples
excelvbaexcel-formulasubstitution

How can I use the substitution function to change multiple values in a single column into a different text output?

I'm trying to create a formula or macro in Excel that replaces various values in a single column to return a specific text.

I created a substitution formula that worked for one specific text value. I need 36 text values to be substituted with 27 text values.

For example, I have the old text values in the right hand column and the new text values in the left hand column. Most of my values are 1:1 with the exception of "orange" which has 10 values that all need to show up as "orange".

I tried recording a macro by using a substitution formula for each cell, but I have almost 1700 cells in my column to be substituted with the correct value.

Solution

  • If you can repeat that orange value down to the blank rows then this is doable with a formula.

    Consider the following data in A1:B8

    +------------+-----+
| orange     | 123 |
| orange     | 234 |
| orange     | 345 |
| orange     | 456 |
| orange     | 567 |
| orange     |  78 |
| grapefruit | 543 |
| apple      |   5 |
+------------+-----+

    And in cell D1 you have the following string:

    I've got a 123 and a 5 in one hand and a 456 and 543 in the other

    In cell E1 you can drop this formula and it will do the replacement of all the numbers:

    =REDUCE(D1, A1:A8, LAMBDA(a,b, SUBSTITUTE(a, OFFSET(b,0,1), b)))

    outputting:

    I've got a orange and a apple in one hand and a orange and grapefruit in the other

    Reduce() is a cool function that will feed an initial value (D1 in this case) and an array (A1:A8) into a function and collect the results back to spit out the final product. The function in this case is a LAMBDA that calls SUBSTITUTE() with that D1 value and the array. You can kind of think of it as building a deeply nested Substitute(Substitute(Substitute())) type formula dynamically.