Excel - how to VLOOKUP items in a delimited list i

Question

I have an Excel document which consist of different sheets. In one sheet I have column:

| text column        |
|#test2,#test3,#test1|
|#test2, #test1      |
|#test1              |

In another sheet I have a mapping for the values:

|some column| value  |
|#test2     | Value2 |
|#test1     | Value1 |
|#test3     | Value3 |

So I need to have the result:

| text column           |
| Value2, Value3, Value1|
| Value2, Value1        |
| Value1                |

Is this possible to get this by Excel Worksheet functions?

I tried to explode the data but I can have undefined quantity of values in the concrete cell. Also tried to use VLOOKUP but I don't have an idea how to use this because I need to split the values before VLOOKUP.

Can you help me? Thank you.

Answer 1

Because you need some form of iteration, i.e. split a value, perform an operation on each item and rejoin the items - it is unlikely there is a way to do this with Excel functions. If you are happy to use VBA then the user-defined function is quite straight-forward:

Option Explicit

Public Function RemapValues(rngSource As Range, rngLookup As Range, strDelimiter As String) As String

    Dim arrIn() As String
    Dim lngCounter As Long
    Dim arrOut() As String

    ' split input string by supplied delimiter
    arrIn = Split(rngSource.Value, strDelimiter, -1)

    ' set capacity of array storing re-mapped values
    ReDim arrOut(LBound(arrIn) To UBound(arrIn))

    ' loop over input array
    For lngCounter = LBound(arrIn) To UBound(arrIn)
        ' do the vlookup operation
        arrOut(lngCounter) = WorksheetFunction.VLookup(arrIn(lngCounter), rngLookup, 2, 0)
    Next lngCounter

    ' return the re-mapped values
    RemapValues = Join(arrOut, strDelimiter)

End Function

E.g.

Answer 2

I think you will find the SUBSTITUTE function will achieve what you are looking for:

SUBSTITUTE(text, old_text, new_text, [instance_num])

The SUBSTITUTE function syntax has the following arguments:

Text Required. The text or the reference to a cell containing text for which you want to substitute characters.

Old_text Required. The text you want to replace.

New_text Required. The text you want to replace old_text with.

Instance_num Optional. Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

You could nest each SUBSTITUTE but I think that would end up being quite difficult to read and maintain.

My suggested approach of implementing would be, as per the image attached, to incrementally perform the substitution. So starting with the orignal text in column A substitute #test1 for value1 in column B and then substitue #test2 in the text now in column B with a formula in column C etc.

substitute example