Cleaning data

[mazizpour]

Hi, 

I have Country Currency data, (i.e. Bahraini_Dinar) in a column and want to split the currency and country name in two separate fields. I did a Text to Column and split the string by '_' however, the country string is where I have a problem. I don't know how to remove the "i" or the "ian"s from the end of the country name. 

If it matters, my end goal is to pull in the currency abbreviation from a separate file. On this particular file, the country name is correctly typed a long with the currency abbrev.

Some examples

Colombian_Peso
Czech_Koruna
Danish_Krone
Emirati_Dirham
Hong_Kong_Dollar
Hungarian_Forint
Icelandic_Krona

[ChrisTX · Accepted solution]

I don't think you simply need to remove "i" or "ian"

I'm guessing you actually want to change

Hungarian to Hungary

Icelandic to Iceland

Your best option may be to try to manually maintain a mapping table, using a tool like Input Data.

Then use the mapping table in a Join tool, to get the correct country name.

Chris