'Finding and Replacing' Multiple Values Within Single Cells

Question

---

Background/Context

---

Converting Table Values to Values Stored on Another Table

I often convert a table's values (let's call them 'A' Values) to a corresponding value stored on another table ('B' Values).

I use 'Find/Replace' or 'VLOOKUP' or some combination of the two, so long as each cell contains only one value.

Files Containing Multiple Values within Single Cells

I need to convert multiple 'A' values (separated by semicolons) stored together within single cells.
There are usually about 200-300 rows and about 80 unique 'A' values.

---

Illustration

---

Each of the values in Table 1 must be converted to the values in Table 2.

Table 1: 'A' Values

'A' Values
A123; A456; A789
A789; A098; A123
A456
A123; A456
A456
And So On, For about 300 Rows, with Around 80 Unique Values

Table 2: Crosswalk: 'A' Values to 'B' Values

'A' Value	'B' Value
A123	B123
A456	B456
A789	B789
And So On...	And So On...

Table 3: End Result (A Values Converted to B Values)

B Values
B123; B456; B789
B789; B098; B123
B456
And So On...

---

Analysis

---

Given that the 'A' Values are stored together in Table 1, methods such as Excel's Find/Replace or VLOOKUP are not immediately available to pull in the corresponding 'B' Values.

I have a workaround whereby I use Excel's 'Text to Columns' to divide Table 1's rows into single cells, and then use a VLOOKUP to pull in the 'B' Value, and then use a Concatenate function to stitch the rows back together again, but it's a hassle, and prone to human error.

---

Question

---

Might I with VBA automatically replace all of Table 1's 'A' values with their corresponding 'B' values?

Answer 1

You could definitely do something like this with VBA. Although if you're not already a coder I'd recommend you make a spreadsheet with 4 tabs:

1. In the first tab have your original data output (for reference & checking)
2. In the second tab paste the data again and use text to columns
3. In the third tab have the vlookups already set up
4. In the fourth tab put your vlookup table

This now becomes a 2 step process: paste the data in, then do text to columns. Hopefully this will reduce human error

Dangers to watch out for:

1. If you have thousands of rows of data the calculations could be too burdensome for your laptop. In this case, set calculations to manual (see here https://www.ablebits.com/office-addins-blog/2017/06/29/excel-calculations-automatic-manual-iterative/ ). You will need to hit Calculate Now when once the data is ready.
2. The text to columns might move the cell references around - leading to #REF errors in the formula tab. If this happens then do text to columns in the first tab, and copy into the second

Answer 2

Replace Multiple Cell Values in Tables Using a Dictionary

Option Explicit

Sub replaceValues()
    
    ' Define constants.
    Const dstName As String = "Sheet1"
    Const dstTbl As String = "Table1"
    Const dstCol As String = "A Values"
    Const Delimiter As String = "; "
    Const srcName As String = "Sheet2"
    Const srcTbl As String = "Table2"
    Const srcKey As String = "A Value"
    Const srcVal As String = "B Value"
    
    ' Define workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    ' Write values from Source Column Ranges to Source Arrays.
    Dim sKey As Variant ' Source Key Array
    Dim sVal As Variant ' Source Value Array
    With wb.Worksheets(srcName).ListObjects(srcTbl)
        sKey = .ListColumns(srcKey).DataBodyRange.Value
        sVal = .ListColumns(srcVal).DataBodyRange.Value
    End With
    
    ' Write values from Source Arrays to Unique Dictionary.
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    Dim i As Long ' Arrays Rows Counter
    For i = 1 To UBound(sKey)
        dict(sKey(i, 1)) = sVal(i, 1)
    Next i
    Erase sKey
    Erase sVal
    
    ' Define Destination Column Range.
    Dim rng As Range
    With wb.Worksheets(dstName).ListObjects(dstTbl)
        Set rng = .ListColumns(dstCol).DataBodyRange
    End With
    
    ' Write values from Destination Column Range to Data Array.
    Dim Data As Variant: Data = rng.Value
    
    ' Declare additional variables.
    Dim cSplit() As String ' Current Split Array
    Dim n As Long ' Current Split Array Element Counter
    Dim cString As String ' Current String (in Split Array)
    
    ' Modify (replace) values in Data Array (using Unique Dictionary).
    For i = 1 To UBound(Data, 1)
        cSplit = Split(Data(i, 1), Delimiter)
        For n = 0 To UBound(cSplit)
            cString = cSplit(n)
            If dict.Exists(cString) Then
                cSplit(n) = dict(cString)
            End If
        Next n
        Data(i, 1) = Join(cSplit, Delimiter)
    Next i
    
    ' Write values from Data Array to Destination Column Range.
    rng.Value = Data
   
End Sub