How to find value in a cell with comma separated list in Excel?
Supposing you have a column contains comma separated values such as Sales,123,AAA, and now you want to find if value 123 in the comma separated cell, how can you do? This article will introduce method to solve the problem.
Recommended Excel Productivity Tools
The following formula can help you finding value in a cell with comma-separated list in Excel. Please do as follows.
1. Select a blank cell, enter formula =IF(ISNUMBER(SEARCH(123,A2)),"yes","no") into the Formula Bar and then press the Enter key. See screenshot:
Note: in the formula, A2 is the cell contains the comma-separated values you will find.
2. Keep selecting the result cell and drag the Fill Handle down to get all results. If value “123” is in the comma-separated cells, you will get the result as “Yes”; otherwise, you will get the result as “No”. See screenshot:
- How to find and replace all blank cells with certain number or text in Excel?
- How to replace commas with newlines (Alt + Enter) in cells in Excel?
- How to split comma separated values into rows or columns in Excel?
- How to add comma at end of cell/text in Excel?
- How to remove all commas in Excel?