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.
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.