Replace letters with Abbreviations

[linga]

Dear All,

Sheet 1 contains the Abbreviation & Sheet 2 contains the values in the cells. Is it possible to automatically replace the matching values with the Abbreviations.

Regards,
Linga

 

[Nunes]

Hi

It's not a very elegant solution, but you could concatenate SUBSITUTEs inside SUBSTITUTEs, like, in cell B2 of Sheet2 put the following formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet2!A2;Sheet1!$A$2;Sheet1!$B$2);Sheet1!A$3;Sheet1!B$3);Sheet1!$A$4;Sheet1!$B$4);Sheet1!A$5;Sheet1!B$5)

And copy it down as you go.
(don't Forget to replace the ";" in my formula by "," if your regional settings require so, my formula separator is ";")

It's 4 SUBSTITUTEs, one for each of the terms you want replaced by it's abreviation.

I could do this in a more elegant way with FIND() and REPLACE(), i believe, if we had the guarantee that each term would appear only once, but this SUBSTITUE way will do the trick, I believe.

 

[Xiq]

Hi linga,

Not sure if there is a smarter way of doing without VBA, but a nested SUBSTITUTE formula might be an option (see below).

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,Sheet1!$A$2,Sheet1!$B$2),Sheet1!$A$3,Sheet1!$B$3),Sheet1!$A$4,Sheet1!$B$4),Sheet1!$A$5,Sheet1!$B$5)

 

[linga]

Hi

It's not a very elegant solution, but you could concatenate SUBSITUTEs inside SUBSTITUTEs, like, in cell B2 of Sheet2 put the following formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet2!A2;Sheet1!$A$2;Sheet1!$B$2);Sheet1!A$3;Sheet1!B$3);Sheet1!$A$4;Sheet1!$B$4);Sheet1!A$5;Sheet1!B$5)

And copy it down as you go.
(don't Forget to replace the ";" in my formula by "," if your regional settings require so, my formula separator is ";")

It's 4 SUBSTITUTEs, one for each of the terms you want replaced by it's abreviation.

I could do this in a more elegant way with FIND() and REPLACE(), i believe, if we had the guarantee that each term would appear only once, but this SUBSTITUE way will do the trick, I believe.

Hi

It's not a very elegant solution, but you could concatenate SUBSITUTEs inside SUBSTITUTEs, like, in cell B2 of Sheet2 put the following formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet2!A2;Sheet1!$A$2;Sheet1!$B$2);Sheet1!A$3;Sheet1!B$3);Sheet1!$A$4;Sheet1!$B$4);Sheet1!A$5;Sheet1!B$5)

And copy it down as you go.
(don't Forget to replace the ";" in my formula by "," if your regional settings require so, my formula separator is ";")

It's 4 SUBSTITUTEs, one for each of the terms you want replaced by it's abreviation.

I could do this in a more elegant way with FIND() and REPLACE(), i believe, if we had the guarantee that each term would appear only once, but this SUBSTITUE way will do the trick, I believe.

Hi Nunes,

Thank you for the reply. Can you help me with multiple values to be found and replaced. Attached the file for reference.

Linga