ThreeWave Character Tests In Formulas

This page describes formulas you can use when working with text strings.
ShortFadeBar

Introduction

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.

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

Count Of Numeric Character

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

download You can download the workbook with all the example formulas on this page.
ShortFadeBar
LastUpdate This page last updated: 8-January-2011.

-->