• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Replace letters with Abbreviations

linga

New Member
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
 

Attachments

  • Test Data.xlsx
    8.9 KB · Views: 2
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 linga,

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

Code:
=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)
 
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
 

Attachments

  • Test Data_Rev.xlsx
    10.1 KB · Views: 3
Back
Top