Replace, Substitute, and Regexreplace are the text functions to replace text strings in Google Sheets. But sometimes you can use Vlookup too! You can replace multiple comma-separated values in Google Sheets with other values in the following ways.

1. Using a nested Substitute formula.
2. Using a Vlookup Combination formula.

I have included both the formulas in this Google Sheets tutorial, but I prefer the Vlookup over SUBSTITUTE.

Though both have their plus and minuses, Vlookup is better as it can easily handle a large set of data.

Update on 03-12-2021:- Other than the above formula options 1 and 2, I have included one more formula (option 3), which is more dynamic.

Where is this going to work?

If you are looking for a way to replace comma-separated product IDs with matching product names or vice versa, you can follow these tips. 

No doubt, in any similar scenarios, you can use any of the below formulas.

In the above example, I have multiple comma-separated values in column A, which are product IDs.

We can match (lookup) these product IDs with the equivalent/matching products with the help of the table in the range D4:E8.

For this, we can use a formula which you can find below. The formula output is in column B.

How to Replace Multiple Comma Separated Values in Google Sheets

First, let’s use the SUBSTITUTE function to replace comma-separated values in Google Sheets as above. Here is that formula.

Option 1: Nested SUBSTITUTE Formula

Make sure that the cells in the range B2:B are blank. Use the below formula in cell B2.

=ArrayFormula(
     SUBSTITUTE(
        SUBSTITUTE(
           SUBSTITUTE(
              SUBSTITUTE(
                 A2:A,"IDA1001","Apple"
              ),"IDM2002","Mango"
           ),"IDO2001","Orange"
        ),"IDB1111","Banana"
     )
)

It’s the same above formula. But, here, the IDs and Products are replaced by cell references in D5:E8.

=ArrayFormula(
     SUBSTITUTE(
        SUBSTITUTE(
           SUBSTITUTE(
              SUBSTITUTE(
                 A2:A,D5,E5
              ),D6,E6
           ),D7,E7
        ),D8,E8
     )
)

Use any of the above formulas to replace multiple comma-separated values in Google Sheets.

Pros:-

• It’s an array formula that expands.

Cons:-

• If the number of replacement texts is large, there are chances of typos. Also, it would be a hefty task to build the formula. In short, the range A2:A is open, but the lookup table D5:E8 is closed.

Option 2: Vlookup Formula to Replace Multiple Comma Separated Values

Here I am not using the REPLACE, REGEXREPLACE, or SUBSTITUTE text replacement functions! Then?

Instead, you can see a lookup and replace approach below.

You can use this Vlookup formula to replace multiple comma-separated values in Google Sheets.

I am recommending this formula since it can handle a large number of IDs and Products without any modification.

=ArrayFormula(
     textjoin(
        ", ",true,
        ifna(
           vlookup(
              TRANSPOSE(trim(split(A2,","))),
              {$D$5:$D&"",$E$5:$E},2,0
           )
        )
     )
)

How to use this Vlookup to substitute multiple Comma-separated values?

Update:-

I have slightly modified this formula. Replaced JOIN with TEXTJOIN and included an IFNA additionally. So you may find a different one on the screenshot below.

Apply the formula in cell B2 and drag it down.

Pros:-

• Supports an ‘unlimited’ number of replacement values (D5:E) without touching the formula. In other words, the lookup table D5:E8 is open.

Cons:-

• It is not providing an expanded array result. You may copy-paste the formula to apply in multiple rows. I mean, in the above example, you need to insert this Vlookup formula in cell B2 and then drag it down.
• The formula will replace mismatching values with blank.
• In cell A4, if the value is “IDB1, IDB1111, IDB2, IDA1001,” the formula will return “Banana, Apple.”

Option 3: Dynamic Array Formula to Replace Multiple Comma Separated Values

A couple of months back, Google has officially introduced the function FLATTEN in Sheets.

That made me possible to code another formula with two open ranges.

Here both A2:A and D5:D are open. So let me call it a dynamic formula to replace multiple comma-separated values in Google Sheets.

Insert the below dynamic range formula in cell B2. It will expand down.

=ArrayFormula(
     regexreplace(
        flatten(trim(split(textjoin(" ",false,
           ifna(
              vlookup(
                 flatten(split(substitute(filter(A2:A,A2:A<>"")&" |",",","")," ")),
                 D5:E,2,0
              ),
              flatten(split(substitute(filter(A2:A,A2:A<>"")&" |",",","")," "))
           )
        ),"|"))),
        "\s", ", "
     )
)

Pros:-

• Similar to option # 1, it’s an array formula. So it expands down without copy-paste.
• It addresses one issue that we face with the option # 1 formula. There only the range A2:A is open. But here, I have specified infinite or opened ranges in the lookup table (D5:E) and search keys (A2:A).
• It addresses two issues with the option # 2 formula.
  • There we have used a non-array (copy-paste) formula. Here we have an array formula.
  • There the formula replaces mismatching values with blank. Here, on the contrary, the formula will leave the mismatching values as it is.

Cons:-

• You may not leave any blank cells between the values in the range A2:A.

That’s all. This way, you can replace multiple strings in a cell/column in Google Sheets.

Example Sheet 031221