Excel Substitutions

Richard Tillman 405

In col A, I have

old1

old2

old3

...

In col B, I have

new1

new2

new3

...

In D1, I have:

=substitute(C1,A1,B1)

so that text entered in C1 is shown in D1 with old1 substituted by new1.

But, for that single cell C1, I want to apply every listed sub. E.g., Entering "id old1 id old3" in C1 yields "id new1 id new3" in D1

Longform, one method for this would be D1:

=substitute(substitute(substitute(C1,A1,B1),A2,B2),A3,B3)

for the first 3 substitutions. How can I implement this effect efficiently, for however long the substitution list is, whether by generating a nested substitution like that, or with a different method — but just using sheet formulas, without using a macro please.

0 comments

Answer accepted by question author

Ashish Mathur 102.5K Volunteer Moderator

Hi,

Enter this formula in cell D2

=REDUCE(C2,$A$2:$A$4,LAMBDA(a,b,SUBSTITUTE(a,b,XLOOKUP(b,$A$2:$A$4,$B$2:$B$4))))

Hope this helps.

User's image

Richard Tillman 405 Reputation points

2026-09-30T21:50:54.9966667+00:00

So to start it all in row 1, do I just move that all up a row? That is, put that in D1 instead of D2, and change all the $2 (representing the start of the sub list?) to $1 ?

Also, does the $4 represent the bottom of the sub list, so if my sub list is 10 items long I just make the $4 into $10 ? Thanks.
Ashish Mathur 102.5K Reputation points Volunteer Moderator

2026-09-30T22:52:30.72+00:00

Yes to both.
Richard Tillman 405 Reputation points

2026-10-01T17:41:30.2966667+00:00

Thank you
Richard Tillman 405 Reputation points

2026-10-02T16:34:28.75+00:00

If I have a whole Word document that I want to apply all the subs to, could I somehow make cell C1 a word box, and paste the whole Word document into C1, so the single D1 formula applies to it all (without having to repeat the formula)?
Ashish Mathur 102.5K Reputation points Volunteer Moderator

2026-10-02T22:45:51.9566667+00:00

Paste the long text in cell C1 and try it yourself. There is a limit of the number of characters that can be accommodated in a cell though

Answer accepted by question author

Jeronimo Fuerte 46,645 Independent Advisor

You can use REDUCE together with LAMBDA to apply each substitution in sequence. For example, if your substitutions are in A1 and B1, and the original text is in C1, try:

=LET(old,FILTER(A1,A1<>""),new,FILTER(B1,A1<>""),REDUCE(C1,SEQUENCE(ROWS(old)),LAMBDA(txt,i,SUBSTITUTE(txt,INDEX(old,i),INDEX(new,i)))))

This first removes any unused rows from the substitution list and then applies each old/new pair one after another. Therefore, if C1 contains id old1 id old3, with old1/new1 and old3/new3 in columns A and B, the result will be id new1 id new3. You can increase A1 and B1 as needed, or preferably convert the substitution list to an Excel Table if it will continue growing.

Jeronimo Fuerte 46,645 Reputation points Independent Advisor

2026-09-30T23:31:15.2233333+00:00

If you want the list to start in row 1, the formula can go in D1 and use C1 as the original text.

However, rather than having to change $4 to $10, $20, etc. every time your substitution list grows, I would recommend making the substitution range large enough in advance, for example:

=LET(old,FILTER($A$1:$A$1000,$A$1:$A$1000<>""),new,FILTER($B$1:$B$1000,$A$1:$A$1000<>""),REDUCE(C1,SEQUENCE(ROWS(old)),LAMBDA(txt,i,SUBSTITUTE(txt,INDEX(old,i),INDEX(new,i)))))

This checks A1 for substitution values, ignores the unused blank rows, and applies each corresponding replacement from column B in sequence. So you can simply keep adding substitutions without changing the formula.
Richard Tillman 405 Reputation points

2026-10-01T17:37:26.4966667+00:00

Now I get it. I hadn't been using a range in yours. One followup: to have my substitution list on a different sheet than the formula, is it something like SublistSheet!$A$100:SublistSheet!$A$100 in the formula? I.e., just replace each $A & $B in your formula with SublistSheet!$A or B?

1 additional answer

Dana D 100 Reputation points

2026-10-01T03:13:32.7033333+00:00

[EDITED] Has a solution.
Was this answer helpful?

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Excel Substitutions

1 additional answer

Your answer