LEN, SEARCH and wildcards
1 / 5
Which names use the short form "Ltd"? Show "Yes" if the name contains "ltd" in any capitals. "Limited" in full is a No.
keyboard Typing formulas is easiest on a computer with a keyboard.
A1
| A | B | C | |
|---|---|---|---|
| 1 | Supplier | Ltd? | |
| 2 | Lumen Limited | ||
| 3 | Meridian Group | ||
| 4 | Delta Limited | ||
| 5 | Bolt Group | ||
| 6 | Harbour Ltd | ||
| 7 | Nimbus LTD | ||
| 8 | Fernwood Ltd | ||
| 9 | Ember Ltd | ||
| 10 | |||
| 11 | |||
| 12 | |||
| 13 |
names
Yes or No
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
How LEN and SEARCH and COUNTIF works
How do you measure text?
LENcounts the characters in a cell, spaces included:=LEN(A2). It's handy for spotting codes that are too short, or have a space on the end.How do you find a word inside text?
SEARCHgives the position where the word starts:=SEARCH("ltd",A2). If it isn't there, you get the error #VALUE!.SEARCHignores capitals;FINDdoes the same job but is case-sensitive.What are wildcards?
In
COUNTIFand similar functions,*means "any characters" and?means "exactly one character".=COUNTIF(A2:A9,"*ltd*")counts the names with "ltd" anywhere in them.
Know someone fighting with a spreadsheet? Send them this page.