Gå til hovedindhold

Hvordan finder man værdi i en celle med kommaadskilt liste i Excel?

Hvis du antager, at du har en kolonne, indeholder kommaseparerede værdier såsom Salg, 123, AAA, og nu vil du finde ud af, om værdi 123 i den kommaadskilte celle, hvordan kan du gøre det? Denne artikel introducerer metode til løsning af problemet.

Find værdi i en celle med en kommasepareret liste med formlen

Find værdi i en celle med en kommasepareret liste med formlen

Den følgende formel kan hjælpe dig med at finde værdi i en celle med kommaadskilt liste i Excel. Gør som følger.

1. Vælg en tom celle, indtast formlen =IF(ISNUMBER(SEARCH(123,A2)),"yes","no") ind i formellinjen, og tryk derefter på Enter-tasten. Se skærmbillede:

Bemærk: i formlen er A2 cellen indeholder de komma-adskilte værdier, du finder.

2. Bliv ved med at vælge resultatcellen, og træk udfyldningshåndtaget ned for at få alle resultater. Hvis værdien "123" er i de komma-adskilte celler, får du resultatet som "Ja"; Ellers får du resultatet som "Nej". Se skærmbillede:

Relaterede artikler:

Bedste kontorproduktivitetsværktøjer

Populære funktioner: Find, fremhæv eller identificer dubletter   |  Slet tomme rækker   |  Kombiner kolonner eller celler uden at miste data   |   Runde uden formel ...
Super opslag: VLookup med flere kriterier  |   Multiple Value VLookup  |   VOpslag på tværs af flere ark   |   Fuzzy Lookup ....
Avanceret rulleliste: Opret hurtigt rulleliste   |  Afhængig rulleliste   |  Multivælg rulleliste ....
Column Manager: Tilføj et bestemt antal kolonner  |  Flyt kolonner  |  Skift synlighedsstatus for skjulte kolonner  |  Sammenlign områder og kolonner ...
Fremhævede funktioner: Grid fokus   |  Designvisning   |   Stor Formel Bar   |  Arbejdsbog & Ark Manager   |  Ressourcebibliotek (Autotekst)   |  Datovælger   |  Kombiner regneark   |  Krypter/Dekrypter celler   |  Send e-mails efter liste   |  Superfilter   |   Specielt filter (filter fed/kursiv/gennemstreget...) ...
Top 15 værktøjssæt:  12 tekst Værktøj (tilføje tekst, Fjern tegn, ...)   |   50 + Chart Typer (Gantt kort, ...)   |   40+ Praktisk formler (Beregn alder baseret på fødselsdag, ...)   |   19 Indsættelse Værktøj (Indsæt QR-kode, Indsæt billede fra sti, ...)   |   12 Konvertering Værktøj (Tal til ord, Valutaomregning, ...)   |   7 Flet og del Værktøj (Avancerede kombinere rækker, Opdel celler, ...)   |   ... og mere

Overlad dine Excel-færdigheder med Kutools for Excel, og oplev effektivitet som aldrig før. Kutools for Excel Tilbyder over 300 avancerede funktioner for at øge produktiviteten og spare tid.  Klik her for at få den funktion, du har mest brug for...

kte-fane 201905

Office Tab Giver fanebladsgrænseflade til Office og gør dit arbejde meget nemmere

  • Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
  • Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
  • Øger din produktivitet med 50 % og reducerer hundredvis af museklik for dig hver dag!
Comments (16)
No ratings yet. Be the first to rate!
Hassam
Hassam
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
Mohini Verma
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
Aahad Memon
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
Scott S.
Scott S.
This comment was minimized by the moderator on the site
Hello!
This is a really cool formula, but it has ... flaw? ... that I cannot figure out how to work around it. "Flaw" is not the correct word, but I cannot think (it's 8:30 am on Monday) of the right word.
When you are looking for any part of a number, you get "yes". So, the following all return "yes":
=IF(ISNUMBER(SEARCH(12,A2)),"yes","no")
=IF(ISNUMBER(SEARCH(23,A2)),"yes","no")
=IF(ISNUMBER(SEARCH(3,A2)),"yes","no")

I tried wrapping the numbers in quotes to no avail. The formula works perfectly for the example's use case, in which all of the numbers are of the same length. But if you are looking for a shorter number that happens to appear in the string, you get a false positive.
Is there an alternative to "SEARCH" that is more literal (again, is that the word? haha)? 
Thanks! 
Reply
1
0
crystal
crystal     Scott S.
This comment was minimized by the moderator on the site
Hi Scott S.,Do you mean search for the exact string?
Reply
Report
0
0
Scott S.
Scott S.     crystal
This comment was minimized by the moderator on the site
Hi, Crystal.
Yes, I suppose that is the way to phrase it. 
The current solution will return “Yes” for any part of the number string. So the numbers 1, 2, 3, 12, 23, and 123 will all return “Yes”. 
I have been trying to come up with a solution that will search a string of numbers and return “Yes” for an exact match. 
Reply
0
0
crystal
crystal     Scott S.
This comment was minimized by the moderator on the site
Hi Scott S,Thanks for your feedback. I haven't found the solution yet and will get back to you if I found it.
Reply
Report
0
0
m.swartz55
m.swartz55     crystal
This comment was minimized by the moderator on the site
I have found a solution. If you add a comma to the left and right of your list it will require the search function to find an exact match from comma to comma. See example below:
=IF(ISNUMBER(SEARCH(",12,",A2)),"yes","no")

,312,123,10,112,126,
This will return false because there is no variable from comma to comma (,12,) that matches the search. 
If you need further clarification let me know and ill post a better example.I was having the same problem when automating a report for my boss and had an epiphany. :)
Reply
Report
0
0
Hassam
Hassam     m.swartz55
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
0
0
Mariah S.
Mariah S.     crystal
This comment was minimized by the moderator on the site
I have found a solution for this. If you add a comma to the left and right of your list and include them (like a sandwich) in your search it'll allow you to find an exact match. Example below:
,123,231,321,122,321,1,2,3,23,
SEARCH(",23,")
This way it forces the search to match from one comma to the other rather than just part of the entry from the list. Hope this is what you were looking for?
Reply
0
0
tapastkrtapan
tapastkrtapan
This comment was minimized by the moderator on the site
Good Morning to all.I have a excel question that how to count comma separated specific values in a single cell in excel.I want to count no of 15's in a single cell that contains 15,215,15,155,45,1515,15 I need the result 3. Is there any formula in excel.
Reply
Report
0
0
m.swartz55
m.swartz55     tapastkrtapan
This comment was minimized by the moderator on the site
Yes, there is a function in Excel that can do this for you. However, as explained in the problem above to gain better accuracy for your data add a comma to the left and right of your list and include it in your search. See example below:
,15,215,15,155,45,1515,15,
=COUNTIF(A1, ",15,")
The explanation of this formula is as follows: =COUNTIF(cell number or range, value your looking for/counting)
Let me know if you need a better example.
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations