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

 

[Harry Geda]

Dear Robert,

Thank you for the work.

I downloaded but I can not figure-out how to use it.

It is too complicated for my level.

Thank you,
Harry

 

[Rick Rothstein]

I know you wanted a formula solution rather than a VB one, but here is a UDF (user defined function) for you to consider anyway...

Function ENCODE(ByVal S As String) As String
  Dim X As Long, Parts() As String
  Parts = Split(Replace(S, " ", ""), ",")
  For X = 0 To UBound(Parts)
    S = Replace(S, Parts(X), Range("A1:A300").Find(Parts(X), , , xlWhole).Offset(, 1))
  Next
  ENCODE = S
End Function

To install this UDF, press ALT+F11 to go into the VB editor, then click Insert/Module once there and then copy/paste the above code into the code window that opened up. That's it... the UDF is installed. Now go back to your worksheet and type this formula into D1...

=ENCODE(C1)

 

[Robert Mika]

Herry enter your string into A1