Excel: When a cell contains a character

OVERVIEW

To check whether a cell contains a specific text or number, we need the following functions SEARCH, ISNUMBER and IF. If we want to be case sensitive, we use FIND instead of SEARCH. Alternatively, we can also use the COUNTIF function in combination with wildcards.

FORMULA

=IF(ISNUMBER (SEARCH ("search criterion",A1) ), "contain", "not contain")

HOW THE FORMULA WORKS

Here it is checked whether the “search criterion” appears in cell A1. The search criterion can be a word, text, a number or any other combination of characters.

1) SEARCH(“Criterion”;A1) checks where “Search Criterion” is in cell A1. We either get the position as a number or if there was no match, the #VALUE! error back.

2) With ISNUMBER we convert the result to TRUE or FALSE. Number = TRUE, #VALUE! = FALSE

3) We then use this truth value in our IF function.

ALTERNATIVE WITH CERTAIN CASES

=IF(ISNUMBER(FIND(“criterion”,A1)),”yes”,”no”)

ALTERNATIVE MIT DER ZÄHLENWENN-FUNKTION UND WILDCARDS

=IF(COUNTIF(A1, ""&"search criterion"&"")=1, "contain", "not contain")

Here it is checked whether the “search criterion” appears in cell A1. The search criterion can be a word, text, a number or any other combination of characters.

1) COUNTIF(A1, “search criterion”) counts whether the criterion appears in cell A1. Here we get either 1 (appears) or 0 (does not appear). Here, however, the entire cell content is compared with the search criterion.

2) We therefore need the wildcards or placeholder characters * here. This stands for any number of characters. These placeholders must be in quotation marks and are concatenated with the search criterion using the & character.
““&”Search criterion”&”“
If the search criterion comes from another cell, the following notation is used.
““&B1&”“

SEVERAL CRITERIA

=IF( COUNT( SEARCH( {"criterion1","criterion2"};A1) )> 0; "at least one criterion"; "")

1) We can also feed SEARCH with several criteria. We either write these with curly brackets as an array An array is a collection of data. You can think of this data type as a data holder. Smart information can be found here: https://u-helmich.de/inf/BlueJ/lexikon/A-D/Array-Initialisierung.html directly in the formula or refer to a range of cells.

2) COUNT(SEARCH({“criterion1″,”criterion2”}
We now get two result values ​​back that either #VALUE! or contain numbers. We count these result values ​​with COUNT. As a result, we get back 0, 1 or 2.

3) COUNT(SEARCH({“criterion1″,”criterion2”};A1))>0
If the result is greater than 0, this means that at least one criterion was found.

3b) COUNT(SEARCH({“Criterion1″,”Criterion2”},A1))=2
If the result is equal to 2, it means that both criteria were found.

PRINT NUMBER OF HOW MANY CRITERIA WERE FOUND

=IF( COUNT( SEARCH( {"criterion1", "criterion2", "criterion3"}, A1) ) > 0, COUNT( SEARCH( {"criterion1", "criterion2", "criterion3"}, A11) ) & " Criteria found"; "")

1) Here we search for 3 criteria and get back the number of hits.

2) We then block this number of hits in the result text.

PRINT NUMBER OF HOW MANY CRITERIA WERE FOUND – SHORT WRITING

=LET( formula, COUNT( SEARCH( {"criterion1", "criterion2", "criterion3"}, A1) ), IF( formula > 0, formula & " criteria found", "") )

1) Here we use LET to avoid double formula parts. This has the advantage that we only have to adjust the criteria in one place.

PRACTICAL EXAMPLE DEGREE OF CONFORMITY

In this example we assign a specific text to the number of hits:
0 hits = no match
1 hit = low match
2 hits = average match
3 hits = high match

=LET( number of hits; COUNT( SEARCH( criteria; A1) );
IF( hit count > 0; CHOICE( hit count; "low"; "medium"; "high") & " match"; "no match") )

1) SEARCH(criteria;A1)
Here we are looking for several criteria. This can be e.g. first name, last name, date of birth etc. which are in a search mask or different cells.

2) LET(number of hits;COUNT(SEARCH(criteria;A1));…
With LET we store the number of hits in the variable number of hits, which we use in the rest of the formula.

3) IF(number of hits>0;…
If we have matches, we want different text to be returned depending on the number of matching criteria.

4) CHOOSE(matchcount;”low”;”medium”;”high”)
With the WAHL function, we assign different words depending on the number of hits.
Number of hits 1 = “low”
Number of hits 2 = “medium”
Number of hits 3 = “high”
and concatenate them with further text &” match”

5) If the hit count is equal to zero, we still output “no match” at the end of the IF function.

EXCEL & POWER BI ONLINE TRAINING

Learn Excel step by step and save time when working with Excel with Pivots, Power Query, Power Pivot, Macros & VBA!

Dieser Beitrag ist auch verfügbar auf: Deutsch (German)