The easiest way to replace multiple comma-separated values in Google Sheets is by using the SPLIT and XLOOKUP combo. This method allows you to substitute several comma-separated values at once.

Purpose

Replacing multiple comma-separated values, such as item codes, with their corresponding item names in Google Sheets is a powerful way to make your data more readable and user-friendly.

Whether you’re working with inventory lists, product catalogs, employee databases, or any dataset involving codes, this process can save you time and reduce errors by converting cryptic codes into easily understandable names.

In this guide, we’ll show you how to efficiently replace item codes with item names using built-in functions such as SPLIT and XLOOKUP in Google Sheets.

Example of Replacing Multiple Comma-Separated Values in Google Sheets

Assume you have the following comma-separated item codes in cell A2:

IDA1001, IDM2002, IDO2001

To replace these with the corresponding item names, you should maintain a table that contains item codes in one column and corresponding item names in the second column. Assume D2:D contains item codes and E2:E contains item names. You can then use the following formula in cell B2:

=ArrayFormula(
   LET(
      codes_S, SPLIT(A2, ", "), 
      IFERROR(JOIN(", ", XLOOKUP(codes_S, $D$2:$D, $E$2:$E, codes_S)))
   )
)

How the Formula Works:

• SPLIT(A2, ", "): This splits the comma-separated values into separate columns. It’s named codes_S.
• XLOOKUP(codes_S, $D$2:$D, $E$2:$E, codes_S): The XLOOKUP function searches for codes_S in $D$2:$D and returns the corresponding item names from $E$2:$E. If no match is found, it retains the existing code.
• JOIN(", ", ...): This joins the output back into a comma-separated list.

This formula allows you to replace multiple comma-separated values in Google Sheets efficiently.

Replacing a List of Multiple Comma-Separated Values in a Column

Assume you have comma-separated values in cells A2:A. You can drag the formula from cell B2 to apply it to other rows. However, to automate this process for the entire column, you can use the MAP and LAMBDA functions.

The MAP function maps each value in a given array (in this case, A2:A) and returns a new value by applying a LAMBDA function.

The syntax is:

MAP(array1, [array2, …], lambda)

Now, let’s convert the drag-down formula into a custom LAMBDA function:

LAMBDA(values, 
   ArrayFormula(
      LET(
         codes_S, SPLIT(values, ", "), 
         IFERROR(JOIN(", ", XLOOKUP(codes_S, $D$2:$D, $E$2:$E, codes_S)))
      )
   )
)

Where values represent each element in the array. In this case, the array is A2:A, which we can specify in MAP as follows:

=MAP(A2:A, LAMBDA(values, 
   ArrayFormula(
      LET(
         codes_S, SPLIT(values, ", "), 
         IFERROR(JOIN(", ", XLOOKUP(codes_S, $D$2:$D, $E$2:$E, codes_S)))
      )
   )
))

Apply this formula in cell B2 after clearing the contents of B2:B. This will replace multiple comma-separated values in A2:A all at once.

Resources

• How to Count Comma-Separated Words in a Cell in Google Sheets
• Sum, Count, and Cumulative Sum of Comma-Separated Values in Google Sheets
• Extract Unique Values from a Comma-Separated List in Google Sheets
• VLOOKUP with Comma-Separated Values in Google Sheets
• Comma-Separated Values as Criteria in FILTER – Google Sheets
• Split Comma-Separated Values into Multiple Columns in Google Sheets
• How to Remove Duplicates from Comma-Delimited Strings in Google Sheets
• How to Compare Comma-Separated Values in Google Sheets
• Validate Comma-Separated Numbers Within a Specific Range in Google Sheets