Replace multiple Text in a single line

[richard.lim85]

Hi Excel Experts,

I am in need of better formula to replace a some text in a long line of text

I tried to use Replace + Search + Len but it only helps to change 1 specific text. Is there a formula to replace multiple text into multiple different of text?

My goal is to replace X1 ~ X7 (in column A) to expecting number (in column B)

Is there a better formula or macro to continue to loop if the numbers filled up to X15 and so on

I have attached a sample of text replacement in this forum

Please advise

Thank You very much

Regards,
Richard

[Glenn Kennedy]

Your initial formula is overcomplicated:

=SUBSTITUTE($A$2,A5,B5)

will work. Using formulae, the easiest way is to nest SUBSTITUTE formulae:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2,A5,B5),A6,B6),A7,B7)

adding on as many as needed.

[Glenn Kennedy]

On reflection probably better to use the $'d verrsion:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,$A$5,$B$5),$A$6,$B$6),$A$7,$B$7)

which can then be copied down, if needed.