Character Tests In Formulas
This page describes formulas you can use when working with text strings.
Many applications use text and character functions to test the contents of one cell against
the content of another cell or to extract information from within a text string. The page
describes a number of formulas to carry out these tasks.
All of the formulas on this page are array formulas so you must press
CTRL SHIFT ENTER rather than just ENTER when you first
enter the formula and whenever you edit it later. If you do this correctly, Excel will display the formula
in the formula bar enclosed in curly braces: { }.
See the Array Formulas page for an in-depth discussion of array formulas and their applications.
Suppose in D10 you have a list of characters (no spaces) that you want to
ensure exist in cell D11. You can use the following formula. D11 may
contain other characters besides those in D10, but those in D10 must
exist in D11. Use the following array formula:
=AND(NOT(ISERROR(SEARCH(MID(D10,ROW(INDIRECT("1:"&LEN(D10))),1),D11,1))))
This will return TRUE if all characters in D10 are present in D11.
This will return FALSE if one or more characters in D10 are not present in D11.
Suppose you have in cell D19 by which you want to constrain the value in D20. This formula returns
TRUE if all of the characters in D20 are present in D19. If D20
contains a character not in D19, the result is false.
=AND(NOT(ISERROR(SEARCH(MID(D20,ROW(INDIRECT("1:"&LEN(D20))),1),D19,1))))
The array formula below tests whether D28 contains only characters from D27 and that all the
characters in D27 are used in D28. NOTE: Due to the level of nested functions, this formula will work only in Excel 2007 and later. It will not work in
Excel 2003 and earlier versions.
=AND(AND(NOT(ISERROR(SEARCH(MID(D27,ROW(INDIRECT("1:"&LEN(D27))),1),D28,1)))),
AND(NOT(ISERROR(SEARCH(MID(D28,ROW(INDIRECT("1:"&LEN(D28))),1),D27,1)))))
The following array formula returns the count of the number of characters that occur in both B35 and B36.
=SUM(--(NOT(ISERROR(SEARCH(MID(D35,ROW(INDIRECT("1:"&LEN(D35))),1),D36)))))
The following formula returns the count of characters that are in one string (D42) that are not in another string
(D43).
=IF(LEN(D42)+LEN(D43)=0,0,SUM(--(ISERROR(SEARCH(MID(D42,ROW(INDIRECT("1:"&LEN(D42))),1),D43,1)))))
The following formula returns TRUE if D49 is completely alphabetic (no numbers, spaces, or other characters):
=IF(LEN(D49)=0,TRUE,AND(CODE(UPPER(MID(D49,ROW(INDIRECT("1:"&LEN(D49))),1)))>=65,
CODE(UPPER(MID(D49,ROW(INDIRECT("1:"&LEN(D49))),1)))<=90))
The following formula returns the position of the first numeric character in D55. If no numeric character is present, the result is the
length of the text plus 1.
= IF(LEN(D55)=0,0, MIN(IF(ISNUMBER(--MID(D55,ROW(INDIRECT("1:"&LEN(D55))),1))=FALSE,LEN(D55)+1,ROW(INDIRECT("1:"&LEN(D55))))))
The following formula returns the position of the first alphabetic (not numeric, space, or symbol) character in D61.
= IF(LEN(D61)=0,0, MIN(IF(ISNUMBER(--MID(D61,ROW(INDIRECT("1:"&LEN(D61))),1))=FALSE,ROW(INDIRECT("1:"&LEN(D61))),LEN(D61)+1)))
The following formula counts the number of numeric characters in cell D68
=SUM(IF(ISNUMBER(--MID(D68,ROW(INDIRECT("1:"&LEN(D68))),1)),1,0))
The following formula counts the number of non-numeric characters in cell D73:
=IF(LEN(D73)=0,0, SUM(IF(ISNUMBER(--MID(D73,ROW(INDIRECT("1:"&LEN(D73))),1)),0,1)))
This formula counts the number of times the string in D78 occurs in the text in D79:
= IF(D78=0,0,(LEN(D79)-LEN(SUBSTITUTE(D79,D78,"")))/LEN(D78))
The following formula returns TRUE or FALSE indicating whether the text in
D100 contains numeric characters.
=SUM(IF(ISNUMBER(--MID(D100,ROW(INDIRECT("1:"&LEN(D100))),1)),1,0))<>0
This formula returns the characters in D106 to the left of the first occurrence of the character
in cell D105:
=IF(ISERROR(SEARCH(D105,D106,1)),"",LEFT(D106,FIND(D105,D106,1)-1))
The following formula returns the characters to the right of the first occurrence in cell 112 of the character in
cell D111:
= IF(LEN(D111)=0,"",IF(ISERROR(SEARCH(D111,D112,1)),"",MID(D112,SEARCH(D111,D112,1)+1,LEN(D112))))
|
This page last updated: 8-January-2011. |