Replace data in string of text with corresponding data from table

Question

I've got a string of text which are a grouping of circuit numbers. For instance, "2,4,6,8"

Each of these are independent circuits with independent values (ampacities).

I have these values all described in a table wherein one column is the CKT # (2) and the column to its right is the ampacity (20).

So if I have circuits 2, 4, 6, and 8, and they have ampacities of 20, 25, 30, & 40 respectively, I want Excel to be able to look at the value for "2" and replace it with "20", and 4, "25", 6, "30", and so on.

So I'd start with "2,4,6,8" and end with "20,25,30,40"

Is there a way to do this?

Thank you!

Answer 1

First enter the following user defined function in a standard module:

Public Function xLate(s As String, rng As Range) As String
    Dim temp As String, arr(), i As Long

    temp = "," & s & ","
    arr = rng
    For i = LBound(arr, 1) To UBound(arr, 1)
        temp = Replace(temp, "," & arr(i, 1) & ",", "," & arr(i, 2) & ",")
    Next i
    xLate = Mid(temp, 2, Len(temp) - 2)
End Function

Then place your translation table somewhere in the worksheet (say F1:G6). Then with data in A1, in B1 enter:

=xlate(A1,F1:G6)

As you see from the example, the function replaces each element in a comma separated string with another element defined in the table.

If a source element does not appear in the table, it is left unchanged.