using split function how to do vlookup and concatenate this value to gether

[gok.nat]

#1: SPLITTING A CELL: I have a column alpha numeric values. They are entered as some characters, separated by "-" symbol, then some characters (i.e. A123-B1234-C12). I want to split this values and after splitted this values need to Vlookup and concatenate all the values in a separate cell. how to do this?

For Example,

one cell value = A123-B1234-C12

other sheet / workbook values
AColumn BColumn
A123 test
B1234 test1
C12 test2

In another column I need result will be
test-test1-test2
Can anyone please help me, that would be greatly appreciated.
Thanks,
Gok

 

[NARAYANK991]

Hi Gok ,

Generally , concatenation of text is difficult to do using formulae , unless the format is standardized.

Will your column always contain 3 items of text separated by hyphens , or is the number of items variable i.e. some cells will contain just one item , some will contain 2 items separated by one hyphen , while others can have 3 , 4 or more items ?

Otherwise , we will need to use helper columns to store intermediate results. Is this acceptable ?

Narayan

 

[gok.nat]

Hi Gok ,

Generally , concatenation of text is difficult to do using formulae , unless the format is standardized.

Will your column always contain 3 items of text separated by hyphens , or is the number of items variable i.e. some cells will contain just one item , some will contain 2 items separated by one hyphen , while others can have 3 , 4 or more items ?

Otherwise , we will need to use helper columns to store intermediate results. Is this acceptable ?

Narayan

Hi Narayan,
Thanks for the reply, I accept this solution.