How to lookup the comma separated data in one cell and return values in another single cell

[Harry Geda]

Hello,

I enter bunch of serial numbers into one cell. 123, 224, 139, 124
There is a table with corresponding numbers. Like 123 = AKM
124=KLM 139=CSA & 244=LMN (please do not try to solve this. They are random letters for example )
the answer should come back as AKM, LMN, CSA, KLM in one cell.
A B
---- -----
123 AKM
124 KLM
.
.
139 CSA
.
.
244 LMN

Lets say they are from A1 to B300
The Serial numbers are entered in D1 and I like to get answers in E1.
example D1 [ 123, 139 ] answer in E1 [AKM, CSA]

I would like to do this with equation instead of VBA if possible.

Thank you,
Harry

 

[Robert Mika]

Is the any finite number of coma entries in your cell?

 

[Harry Geda]

Hello Robert,
The maximum number of entries will not exceed 20 per cell.

Thank you for looking into this.
Harry

 

[Robert Mika]

I hope you are using X07 or 10( if not please let me know)

A1: your data

A4:=", "&A1&","

A5:=IFERROR(MID(SUBSTITUTE(SUBSTITUTE($A$4,", ","&",ROW(A1)),",","%",ROW(A1)),FIND("&",SUBSTITUTE($A$4,", ","&",ROW(A1)))+1,FIND("%",SUBSTITUTE(SUBSTITUTE($A$4,", ","&",ROW(A1)),",","%",ROW(A1)))-1-FIND("&",SUBSTITUTE($A$4,", ","&",ROW(A1)))),"")

copy down

B5:=IF(A5="","",VLOOKUP(A5,$D$1:$E$5,2,0))

copy down

 

[Harry Geda]

Hello Robert,

I am confused with the solution.
Which one is my data source?
AB column pair or DE pair?

The source cell as you wrote: 123,227884, 137779, 124, 3455544
The resulting cell should read: kkk, ddd, sss, www, ggg
I did not find the resulting cell in your example.

Please advise,
Harry

 

[Robert Mika]

Hi Harry,
Sorry I have missed this point.
You need to dowload Morefunction Add-in

http://www.freewarefiles.com/downloads_counter.php?programid=14922

and use MCONCAT function.
Unfortunaletu this add-in does not work on my PC so I can not show you how to use but you will find explanation once installed,
I hope that helps.

 

[Harry Geda]

Robert,

Thank you for working with me.
I am not allowed to install programs.

Thanks for working on this.
Bye,
Harry

 

[Robert Mika]

On second thought:
In C5 copy dow:
=IF(B5="","",", ")
In A2:
=TRIM(CONCATENATE(B5,C5,B6,C6,B7,C7,B8,C8,B9,C9,B10,C10,B11,C11,B12,C12))
you will need to adjust the CONCATENATE formula to all cells in your examples.

Hope that helps.

 

[Harry Geda]

Robert,
I am still confused where is your data located?
Regards,
Harry

 

[Robert Mika]

Hope this help:
https://rapidshare.com/files/367400325/conca.xlsx