Replacing Abbreviation with a full name

[kushalb]

Hi All,

Thank you for your immense support & help.

I am stuck at this issue where I have a dataset with first 2 columns like mentioned below & need Compiled_Jurisdictions column with the help of mapping data given below:

ATR_UID	Jurisdiction_Abbreviations	Compiled_Jurisdictions
1002314__HPCB	QC	Quebec
1004728__HBUV	BC	British Columbia
1005152__HBKQ	ON	Ontario
1005190__HPOCB	QC	Quebec
1008249__HBCAQ	BC	British Columbia
1013668__HBCZA	CA,ON,BC	Canada, Ontario, British Columbia
1070291_4200037_HBEZ	CA,AB	Canada, Alberta

I have a Jurisdiction mapping file as well which is:

Value in Now	Expected values
ON	Ontario
QC	Quebec
CA	Canada
BC	British Columbia
AB	Alberta
NS	Nova Scotia
MB	Manitoba
SK	Saskatchewan
PE	Prince Edward Island
NL	Newfoundland and Labrador
NB	New Brunswick
NT	Northwest Territories
YT	Yukon
NU	Nunavut

so, how to solve this problem as i tried Find & replace tool but it's not compiling various jurisdiction in a single row.

Thank you in advance

[Qiu · Accepted solution]

@kushalb 
We should use the option "Replace Multiple Found Items" and replicate a new column for output.

[bkurt]

First do a text to columns and separate with delimiter commas into three columns. Then do three joins with the mapping file into the three columns.

[binu_acs]

@kushalb One way of doing this

[Emmanuel_G]

@kushalb 

When you want to replace a value in many rows based on some mapping, the Find and Replace tool is the most suitable tool ( Instead of do if with many subconditions ).

Find in attachement the way to do that with this tool and let us know if it worlks as you want.