Is there a way to use the substitution function in VBA to change multiple values in a single column into a different text output?

Question

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 was able to create a substitution formula that worked for 1 specific text value, the problem is that i need one formula for 36 text values to be substituted with 27 text values. I figured VBA would help me with this, but i'm a complete novice with VBA.

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 that need to be substituted with the correct value. is there a way that VBA can do this for me? I know the coding for it must be massive and I'm not smart enough to figure it out

Answer 1

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.