A B
PEK BEIJING
SHA SHANGHAI
CAN GUANGZHOU
In sheet1 I have a database like the above, the column A is city code, and the column B contains theie corresponding city name.
In sheet2 there are citypairs as follows:
PEK-SHA
PEK-SHA-PEK
PEK-SHA-CAN-SHA
PEK-SHA-CAN-SHA-PEK
What I want to do is convert the citypairs into their actual city names, for example, PEK-SHA-CAN-SHA, I want it to be BEIJING-SHANGHAI-GUANGZHOU-SHANGHAI. I have tried a several time by using functions, mid, left,right,index,match,if, but I failed because it's too long to be kept correct. Can you experts tell me how to do it, VBA and functions are both fine. Thanks in advance.
PEK BEIJING
SHA SHANGHAI
CAN GUANGZHOU
In sheet1 I have a database like the above, the column A is city code, and the column B contains theie corresponding city name.
In sheet2 there are citypairs as follows:
PEK-SHA
PEK-SHA-PEK
PEK-SHA-CAN-SHA
PEK-SHA-CAN-SHA-PEK
What I want to do is convert the citypairs into their actual city names, for example, PEK-SHA-CAN-SHA, I want it to be BEIJING-SHANGHAI-GUANGZHOU-SHANGHAI. I have tried a several time by using functions, mid, left,right,index,match,if, but I failed because it's too long to be kept correct. Can you experts tell me how to do it, VBA and functions are both fine. Thanks in advance.