Přejít k hlavnímu obsahu

Jak najít hodnotu v buňce se seznamem odděleným čárkami v aplikaci Excel?

Předpokládejme, že máte sloupec, který obsahuje hodnoty oddělené čárkami, například Sales, 123, AAA, a nyní chcete zjistit, zda hodnota 123 v buňce oddělené čárkami, jak to můžete udělat? Tento článek představí metodu řešení problému.

Najděte hodnotu v buňce se seznamem odděleným čárkami pomocí vzorce

Najděte hodnotu v buňce se seznamem odděleným čárkami pomocí vzorce

Následující vzorec vám pomůže najít hodnotu v buňce se seznamem odděleným čárkami v aplikaci Excel. Postupujte prosím následovně.

1. Vyberte prázdnou buňku a zadejte vzorec =IF(ISNUMBER(SEARCH(123,A2)),"yes","no") do řádku vzorců a poté stiskněte klávesu Enter. Viz screenshot:

Poznámka: ve vzorci je A2 buňka obsahující hodnoty oddělené čárkami, které najdete.

2. Pokračujte ve výběru buňky výsledků a přetažením rukojeti výplně dolů zobrazte všechny výsledky. Pokud je v buňkách oddělených čárkami hodnota „123“, dostanete výsledek jako „Ano“; jinak získáte výsledek jako „Ne“. Viz snímek obrazovky:

Související články:

Nejlepší nástroje pro produktivitu v kanceláři

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty   |  Odstranit prázdné řádky   |  Kombinujte sloupce nebo buňky bez ztráty dat   |   Kolo bez vzorce ...
Super vyhledávání: Více kritérií VLookup  |   VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte rozevírací seznam   |  Závislý rozbalovací seznam   |  Vícenásobný výběr rozevíracího seznamu ....
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  |  Porovnejte rozsahy a sloupce ...
Doporučené funkce: Zaměření mřížky   |  Návrhové zobrazení   |   Velký Formula Bar   |  Správce sešitů a listů   |  Knihovna zdrojů (Automatický text)   |  Výběr data   |  Zkombinujte pracovní listy   |  Šifrovat/dešifrovat buňky   |  Odesílat e-maily podle seznamu   |  Super filtr   |   Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů:  12 Text Tools (doplnit text, Odebrat znaky, ...)   |   50 + Graf Typ nemovitosti (Ganttův diagram, ...)   |   40+ Praktické Vzorce (Vypočítejte věk na základě narozenin, ...)   |   19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty, ...)   |   12 Konverze Tools (Čísla na slova, Přepočet měny, ...)   |   7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky, ...)   |   ... a více

Rozšiřte své excelentní dovednosti pomocí Kutools for Excela zažijte efektivitu jako nikdy předtím. Kutools for Excel Nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času.  Kliknutím sem získáte funkci, kterou nejvíce potřebujete...

karta kte 201905

Office Tab Přináší rozhraní s kartami do Office a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
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