This Page: www.cpearson.com/Excel/CharacterTests.aspx

Last Updated: 06-Nov-2013

Copyright 1997 - 2014, Charles H. Pearson

Site Last Updated: 24-Feb-2018

Advanced Software

Design And Development

Design And Development

Office Integration Projects
NET Programming
XML Development

Search The Site:

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.

You can download the workbook with all the example formulas on this page. |

Ensure All Given Characters Exist In A String

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.

Ensure Only Given Characters Exist In A String

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))))

Ensure All And Only Characters Exist In A String

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)))))

Count Of Common Characters In Two Strings

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)))))

Count Of Characters In One String And Not Another

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)))))
**

Is As String Completely Alphabetic

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))

Position Of First Numeric Character

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))))))

Position Of First Alphabetic Character

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))

Count Of Non-Numeric Character

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)))

Count Of Substring Within A String

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))

String Contains Numeric Characters

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

Characters To Left Of Given Character

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))

Characters To Right Of Given Character

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))))

You can download the workbook with all the example formulas on this page. |

This page last updated: 8-January-2011. |