How to Put Multiple Values in One Cell in Excel (and Still Work With Them)
10 min read
Excel was built around one value per cell, but real data rarely cooperates. A customer has three phone numbers, a project has four owners, an order lists several products. This guide covers every common way to handle multiple values in one cell in Excel: typing them in, building them with formulas, picking several items from a dropdown, splitting them back out, and looking up, checking or counting what is inside. Each method has a small example you can copy, and a note on which Excel version it needs.
Quick version check
Several of the functions below are fairly new, so check your version before you start. You can see it under File > Account.
| Feature | Works in |
|---|---|
& operator, CHAR(10), SUBSTITUTE, SEARCH |
All versions |
| Flash Fill (Ctrl+E) | Excel 2013 and later |
TEXTJOIN, CONCAT |
Excel 2019 and later, Microsoft 365 |
FILTER, UNIQUE |
Excel 2021 and later, Microsoft 365 |
TEXTSPLIT |
Excel 2024 and Microsoft 365 only |
TEXTSPLIT trips people up the most. Excel 2021 has FILTER but not TEXTSPLIT, so if you type it there you get a #NAME? error.
Type multiple values in one cell with line breaks
The simplest option is to type each value on its own line inside the cell.
- Double-click the cell (or select it and press F2).
- Type the first value.
- Press Alt+Enter on Windows, or Control+Option+Return on a Mac.
- Type the next value and repeat, then press Enter to finish.
Excel turns on Wrap Text automatically when you do this, so the cell grows taller to show every line. If you only press Enter, Excel moves to the next cell instead.
Line breaks are fine for notes and addresses that people read. They are harder to work with in formulas, although the sections on splitting and counting show how to handle them.
Combine values from several cells into one
Often the values already sit in separate columns and you want them together in one cell. Here is a small contact list:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Phone 1 | Phone 2 | Phone 3 |
| 2 | Ana | 091 111 222 | 098 333 444 | |
| 3 | Marko | 095 555 666 | 092 777 888 |
The & operator and CONCAT
The ampersand joins text in any version of Excel:
=B2&", "&C2
Result in row 2: 091 111 222, 098 333 444
CONCAT does the same job and accepts ranges:
=CONCAT(B2,", ",C2)
The weakness of both shows up in row 3. =B3&", "&C3&", "&D3 returns 095 555 666, , 092 777 888, with an empty slot where Phone 2 is missing.
TEXTJOIN skips the blanks
TEXTJOIN takes a delimiter, a TRUE/FALSE switch for ignoring empty cells, and then the range:
=TEXTJOIN(", ", TRUE, B3:D3)
Result: 095 555 666, 092 777 888
This is the formula most people want when they search for how to combine cells. Copy it down and every row gets a clean list, whatever gaps the data has.
Put each value on its own line with a formula
To get the Alt+Enter look from a formula, use CHAR(10) (the line feed character) as the delimiter, then turn on Wrap Text for the cell (Home > Wrap Text):
=TEXTJOIN(CHAR(10), TRUE, B2:D2)
Result in row 2, shown on two lines:
091 111 222
098 333 444
Without Wrap Text the values still contain the break, but Excel shows them on one line.
Select multiple items from a dropdown list
A regular data validation dropdown (Data > Data Validation > List) lets you pick one item. How to pick several, so that a cell reads Excel, Power BI, SQL, is a question that comes up again and again on Microsoft’s Q&A forums and Reddit. Excel has no built-in setting for it, so you need a short VBA macro.
Right-click the sheet tab, choose View Code, and paste this into the sheet’s code window. It assumes your dropdowns are in C2:C100, so change that range to match yours:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oldVal As String, newVal As String
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, Me.Range("C2:C100")) Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub
On Error GoTo ExitHandler
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
If oldVal = "" Then
Target.Value = newVal
ElseIf InStr(1, ", " & oldVal & ", ", ", " & newVal & ", ") = 0 Then
Target.Value = oldVal & ", " & newVal
Else
Target.Value = oldVal
End If
ExitHandler:
Application.EnableEvents = True
End Sub
Each time you pick an item, the macro adds it to what is already in the cell, separated by a comma. Picking an item that is already there does nothing, and pressing Delete clears the cell so you can start over.
A few things to know before you rely on it:
- Save the file as a macro-enabled workbook (.xlsm), or the code is lost when you close it.
- Macros do not run in Excel for the web or the mobile apps. The dropdown still works there, but only for one item.
- Running a macro clears Excel’s undo history, so Ctrl+Z will not take back a selection.
Split one cell into multiple cells
The reverse problem comes up just as often: someone exported a report with all the values crammed into one cell, and you need them in separate columns or rows. Take this column of skills:
| A | |
|---|---|
| 1 | Skills |
| 2 | Excel, Power BI, SQL |
| 3 | Python, Excel |
TEXTSPLIT (Microsoft 365 and Excel 2024)
TEXTSPLIT splits text by a delimiter and spills the pieces into neighboring cells:
=TEXTSPLIT(A2, ", ")
Result: Excel, Power BI and SQL in three cells across the row.
To spill down a column instead, leave the column delimiter empty and use the row delimiter:
=TEXTSPLIT(A2, , ", ")
For values separated by line breaks, split on CHAR(10):
=TEXTSPLIT(A2, CHAR(10))
Because it is a formula, the result updates when the source cell changes. Make sure the cells it spills into are empty, or you get a #SPILL! error.
Text to Columns (any version)
Text to Columns is the older, one-time way to split a column:
- Select the cells, then go to Data > Text to Columns.
- Choose Delimited and click Next.
- Tick Comma (or whichever separator your data uses). For line breaks, tick Other, click in its box and press Ctrl+J.
- Pick a destination cell so the original column is not overwritten, then click Finish.
Text to Columns does not leave a formula behind, so if the source data changes you have to run it again. It also leaves the space after each comma at the start of the next value; wrap the results in TRIM if that matters.
Flash Fill (Excel 2013 and later)
Flash Fill works from an example instead of a rule. Next to the data, type the first value you want to extract (for row 2, type Excel in B2), move to B3 and press Ctrl+E. Excel looks at the pattern and fills in the rest of the column with the first skill from each cell.
It is quick for regular, predictable data. Check the results, though, because Flash Fill guesses and can get uneven rows wrong. Like Text to Columns, it does not update when the data changes.
Look up and return multiple values in one cell
VLOOKUP and XLOOKUP stop at the first match. When a lookup value matches several rows and you want all of them in a single cell, combine TEXTJOIN with FILTER.
| A | B | |
|---|---|---|
| 1 | Customer | Order ID |
| 2 | C-101 | O-9001 |
| 3 | C-102 | O-9002 |
| 4 | C-101 | O-9003 |
| 5 | C-101 | O-9007 |
With the customer ID typed in D2:
=TEXTJOIN(", ", TRUE, FILTER(B2:B5, A2:A5=D2, ""))
Result for C-101: O-9001, O-9003, O-9007
FILTER returns every order ID where the customer column matches D2, and TEXTJOIN joins them with commas. The "" at the end tells FILTER what to return when nothing matches. Without it, a customer with no orders gives a #CALC! error.
Two common variations:
- Remove duplicates by wrapping the filter in
UNIQUE:=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(B2:B5, A2:A5=D2, ""))) - Sort the list with
SORT:=TEXTJOIN(", ", TRUE, SORT(FILTER(B2:B5, A2:A5=D2, "")))
FILTER needs Excel 2021 or Microsoft 365. In Excel 2019, which has TEXTJOIN but not FILTER, an IF array does the same job. Confirm it with Ctrl+Shift+Enter instead of Enter:
=TEXTJOIN(", ", TRUE, IF(A2:A5=D2, B2:B5, ""))
Check if a cell contains one of several values
Say column A holds free-text notes and you want to flag any row that mentions a word from a short list in E2:E4 (urgent, refund, complaint).
| A | B | |
|---|---|---|
| 1 | Note | Flag |
| 2 | Customer asked for a refund | TRUE |
| 3 | Delivery confirmed | FALSE |
| 4 | Urgent: wrong item sent | TRUE |
This formula works in every version:
=SUMPRODUCT(--ISNUMBER(SEARCH($E$2:$E$4, A2)))>0
SEARCH looks for each word inside A2 and returns a position number when it finds one, or an error when it does not. ISNUMBER turns that into TRUE or FALSE, the double minus turns those into 1s and 0s, and SUMPRODUCT adds them up. Anything above zero means at least one word was found.
SEARCH ignores case, which is why row 4 matches Urgent. Use FIND instead if case should matter. Be aware that both match partial words, so refund also matches refunded.
In Microsoft 365 or Excel 2021, a shorter version gives the same result:
=OR(ISNUMBER(SEARCH(E2:E4, A2)))
Count the values in a cell
To count how many comma-separated items sit in one cell, count the commas and add one:
=IF(TRIM(A2)="", 0, LEN(A2)-LEN(SUBSTITUTE(A2, ",", ""))+1)
For Excel, Power BI, SQL the result is 3. SUBSTITUTE removes every comma, so the difference in length is the number of commas. The IF returns 0 for an empty cell instead of 1. For values on separate lines, replace "," with CHAR(10).
With TEXTSPLIT available, this is shorter:
=COUNTA(TEXTSPLIT(A2, ","))
To count how many cells in a range contain a particular value somewhere in their list, use COUNTIF with wildcards:
=COUNTIF(A2:A100, "*Excel*")
With the skills table above, the result is 2, because both rows mention Excel.
Native lists in cells are coming
On September 24, 2026, Microsoft announced lists, arrays in cells and nested arrays for Excel. With lists, you choose Insert > List (or press Ctrl+J) and type items separated by commas, and Excel stores them as separate values inside one cell. You can then filter by individual items and reference the list in calculations.
For now it is an opt-in feature in the Microsoft 365 Insiders Beta Channel for Windows and Mac. Microsoft has not given a date for general release, its early notes list gaps with PivotTables, charts, Power Query and data validation, and it advises against using the feature in important workbooks yet. Until it reaches regular Microsoft 365, the formula methods above are what you can count on, and they work in files you share with people on older versions.
Frequently asked questions
How do I put multiple lines of text in one Excel cell?
Double-click the cell, type the first line, press Alt+Enter (Control+Option+Return on a Mac) and type the next line. To do the same with a formula, join the values with CHAR(10) and turn on Wrap Text.
Can a data validation dropdown allow multiple selections?
Not on its own. Excel’s dropdowns accept one item. A short Worksheet_Change macro, like the one above, adds each new pick to the existing text. The workbook has to be saved as .xlsm and the macro only runs in desktop Excel.
Why does TEXTSPLIT show a #NAME? error?
Your version of Excel does not have the function. TEXTSPLIT is only in Microsoft 365 and Excel 2024. In older versions, use Text to Columns or Flash Fill.
How do I return all matches for a lookup in one cell?
Use =TEXTJOIN(", ", TRUE, FILTER(return_range, lookup_range=value, "")) in Excel 2021 or Microsoft 365. In Excel 2019, use TEXTJOIN with an IF array confirmed with Ctrl+Shift+Enter.
Is storing several values in one cell a good idea?
It is fine for display, notes and reports that people read. For data you need to filter, pivot or summarize, one value per cell (or one row per item) is still easier to work with, and you can always join the values for display with TEXTJOIN.
Need help with a messy workbook?
If your data is full of combined cells that need splitting, or you want a multi-select dropdown set up across a whole workbook, I can build the formulas or macros for you and explain how they work. See Spreadsheet Fix.