{"id":1594,"date":"2020-07-03T16:28:49","date_gmt":"2020-07-03T23:28:49","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=1594"},"modified":"2020-12-23T10:11:20","modified_gmt":"2020-12-23T18:11:20","slug":"how-to-check-excel-if-cell-contains-partial-text","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=1594","title":{"rendered":"How to Check Excel if Cell Contains Partial Text"},"content":{"rendered":"\n

To check if a cell contains specific text<\/strong>, you can use the ISNUMBER and the SEARCH function in Excel<\/strong>. There’s no CONTAINS function in Excel.<\/p>\n\n\n\n

1. To find the position of a substring in a text string, use the\u00a0SEARCH function. <\/p>\n\n\n\n

= SEARCH(B2,A2)<\/p>\n\n\n\n

\"Search<\/figure>\n\n\n\n

Explanation: “duck” found at position 10, “donkey” found at position 1, cell A4 does not contain the word “horse” and “goat” found at position 12.<\/p>\n\n\n\n

2. Add the ISNUMBER function. The ISNUMBER function returns TRUE if a cell contains a number, and FALSE if not. <\/p>\n\n\n\n

= ISNUMBER(SEARCH(B2,A2))<\/p>\n\n\n\n

\"IsNumber<\/figure>\n\n\n\n

Explanation: cell A2 contains<\/strong> the word “duck”, cell A3 contains the word “donkey”, cell A4 does not contain the word “horse” and cell A5 contains the word “goat”.<\/p>\n\n\n\n

3. You can also check if a cell contains specific text, without displaying the substring. Make sure to enclose the substring in double quotation marks.<\/p>\n\n\n\n

= ISNUMBER(SEARCH(“DUCT”,A2))<\/p>\n\n\n\n

\"Double<\/figure>\n\n\n\n

4. To perform a case-sensitive search, replace the SEARCH function with the FIND function.<\/p>\n\n\n\n

= ISNUMBER(FIND(B2,A2))<\/p>\n\n\n\n

\"Case-sensitive<\/figure>\n\n\n\n

Explanation: the formula in cell C3 returns FALSE now. Cell A3 does not contain the word “donkey” but contains the word “Donkey”.<\/p>\n\n\n\n

5. Add the\u00a0IF function. The formula below (case-insensitive) returns “Found” if a cell contains specific text, and “Not Found” if not.<\/p>\n\n\n\n

= IF(ISNUMBER(SEARCH(B2,A2)),”FOUND”,”NOT FOUND”)<\/p>\n\n\n\n

\"Check<\/figure>\n\n\n\n

6. You can also use the IF and the COUNTIF function in Excel to check if a cell contains specific text. However, the COUNTIF function is always case-insensitive.<\/p>\n\n\n\n

= IF(COUNTIF(A2,”*”&B2&”*”), “FOUND”, “NOT FOUND”)<\/p>\n\n\n\n

\"If<\/figure>\n\n\n\n

Explanation: the formula in cell C2 reduces to =IF(COUNTIF(A2,”*duck*”),”Found”,”Not Found”). An asterisk (*) matches a series of zero or more characters. Visit our page about the COUNTIF function to learn all you need to know about this powerful function.<\/p>\n","protected":false},"excerpt":{"rendered":"

To check if a cell contains specific text, you can use the ISNUMBER and the SEARCH function in Excel. There’s no CONTAINS function in Excel. 1. To find the position of a substring in a text string, use the\u00a0SEARCH function. = SEARCH(B2,A2) Explanation: “duck” found at position 10, “donkey” found at position 1, cell A4 does not Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[],"class_list":["post-1594","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/1594","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1594"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/1594\/revisions"}],"predecessor-version":[{"id":2839,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/1594\/revisions\/2839"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1594"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1594"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1594"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}