Skip to main content

Explore the new, free version of Kutools for Outlook today!

How to find value in a cell with comma separated list in Excel?

Author: Siluvia Last Modified: 2020-12-15

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.

Find value in a cell with comma separated list with formula

Find value in a cell with comma separated list with formula

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:

Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (17)
No ratings yet. Be the first to rate!
Iq
Guest
This comment was minimized by the moderator on the site
It’s working for single value, how do I find multiple values. Example: search values (101, 402,110) it might have all 3 or only one or two values and find cell will have any of three or all three or two.
Scenarios
1. Cell 1: 101 cell 2: 101,402,110,202,220
2. Cell 1: 101,402 cell 2: 101,402,202,220,110
3. Cell 1: 110,101,402 cell 2: 101,402,202,220,110

Pls advice
Reply
0
0
Hassam
Guest
This comment was minimized by the moderator on the site
Hey, I still cant figure this out. I am still having the same problem. instead of using number in the formula I am using a cell reference. for example if i am searching for "1"(Q$1) in (123, 25,29,200)($J2) it is still giving me YES. The formula is =IF(ISNUMBER(SEARCH(Q$1,$J2)),"yes","no")

PS: I have a big data set I cannot put commas before and after in every cell. Will appreciate a solution
Reply
0
0
crystal
crystal     Hassam
This comment was minimized by the moderator on the site
Hi Hassam

Just use the following formula without the IF function. If the number matches exactly, you will get the result "TRUE", otherwise you will get the result "FALSE".
=ISNUMBER(FIND(",1,",","&$J2&","))
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/match.png
Reply
Report
0
0
Mohini Verma
Guest
This comment was minimized by the moderator on the site
Hi All, I have a cell that has multiple countries separated by commas
Example : A2 cell value is (India, Japan, Malaysia, Greater China)
Column G has the country names
Now , I need to find look for a formula that compares value from column G to value in A2 cell .
If the value matches, then return true otherwise return False
Reply
0
0
crystal
crystal     Mohini Verma
This comment was minimized by the moderator on the site
Hi Mohini Verma,
The formula can be modified as follows:
=IF(ISNUMBER(SEARCH(G2,A2)),"TRUE","FALSE")
Reply
Report
0
0
duncan
duncan
This comment was minimized by the moderator on the site
How about this please, I need to get the values in A and equate them with the figures in C and return the values in B separated as in commas respectively to C.

Thanks
View Attachments (1 / 5)
Reply
Report
0
0
crystal
crystal     duncan
This comment was minimized by the moderator on the site
Hi duncan,
If you are using Excel for 365, the following formula can help solving the problem.
=TEXTJOIN(",",TRUE,XLOOKUP(TRANSPOSE(VALUE(TEXTSPLIT(D1,","))),A1:A3,B1:B3,"",0,1))
This formula only works in Excel for 365. For other Excel versions other than Excel for 365, I have not found a formula that handles it..
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/find-value.png
Reply
Report
0
0
Aahad Memon
Guest
This comment was minimized by the moderator on the site
Hi Please help

I have table in Excel with two columns A & B, under A there are value Eg A2 Cell has 1,2,2,1,2,1,2 etc ( only has two numbers 1 and 2 but needs to recognise it has only 1 & 2 in the whole cell and no other number) and A3 has 1,1,1 and A4 has 0, A5 has 2,2,2,2 and A6 has 20. i want B2 should return the value for A2 as Asia, B3 Africa, B4 as None, B5 as America and B6 as Europe. I thought i could use the if formula but it is not able to recognise comma separated numbers and return value). Please Help
Reply
0
0
There are no comments posted here yet
Load More
Leave your comments
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations