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 namedcodes_S
.XLOOKUP(codes_S, $D$2:$D, $E$2:$E, codes_S)
: The XLOOKUP function searches forcodes_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
Hello Prashanth,
As usual, your creativity with formulas surprises me every time!
I arrived on this page because I need to replace the content on a cell in a similar way to this one. At least so I thought before trying! =)
Instead of a product list separated by a comma, I have a sentence like:
There is IDM2002, and we also have IDM2001 and IDM2003.
I would like it to become:
There is Mango, and we also have Apple and Orange.
I’m looking for formula because my substitution list is close to 1000 elements, and it will be growing over time.
As far as you know, is there a way to have this behavior within a cell in Google Sheets?
Kind regards,
Matteo
Hi, Matteo,
The same formulas in my tutorial would work. If you face issues, make an example in a new sheet and share that sheet with me via a reply to this thread. I won’t publish your reply with the Sheet link.
Hello Prashanth.
Thanks for the quick reply! I set up a sheet to play with. And I played, but it seems trickier than expected… =/
I’m looking forward to your opinion! =)
Thanks again,
Matteo
Hi, Matteo,
I have tried my level best and could solve the problem partly. I mean I could replace multiple words in a text at the cost of losing some special characters like the dot, comma, exclamation, and the new line.
You may please find the same in the new tab named “kvp” in your file.
Prashanth, that’s a very wonderful formula you wrote there.
And I can now say that—if even you couldn’t find a way to have everything working—it means the issue is simply not solvable through GS.
I learned a lot!
Thank you very much for trying, I really appreciated it!
Matteo
Thanks for leaving your feedback…