This Page: cpearson.com/Excel/stringformulas.aspx

Last Updated: 06-May-2018

Copyright 1997 - 2014, Charles H. Pearson

Site Last Updated: 03-Feb-2021

Advanced Software

Design And Development

Design And Development

Office Integration Projects
NET Programming
XML Development

Search The Site:

Formulas For Working With Strings

This page describes some formulas for working with text strings.

Introduction

This page describes a number of worksheet formulas that work with strings of text.

Counting The Number Of Specific Characters Or Strings Of Characters In A Cell

The following formula will return the number of times that the text in B1 occurs
in the text in A1. This is not case sensitive so, for example, 'A' is
treated the same as 'a'.

=IF(LEN(B1)=0,0,(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),"")))/LEN(B1))

If you want to use a case-sensitve match where for example 'A' is treated
differently than 'a', use the following formula:

=IF(LEN(B1)=0,0,(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1))

Counting The Number Of Letters In A Cell

The following formula counts the number of letters (A to Z, in either upper or lower case) in cell A1.

=IF(LEN(A1)=0,0,SUM(((CODE("A")<=CODE(UPPER(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)))))*((CODE("A")<=CODE(UPPER(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)))))))

This formula is an *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
properly, Excel will display the formula in the formula bar enclosed in curly braces { }. See the
Array Formulas page for more information about array formulas. If A1
is empty, the result is 0.

Counting The Number Of Digits In A Cell

The following formula counts the number of digits (0 to 9) in cell A1.

=IF(LEN(A1)=0,0,SUM(((CODE("0")<=CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))))*((CODE("9")>=CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))))))

This is an array formula so you must enter it with CTRL SHIFT ENTER rather than just
ENTER. If cell A1 is empty, the formula returns 0.

Position Of First Digit In A String

This formula will return the position of the first digit (0 - 9) in the string in A1.

=IF(LEN(A1)=0,0,MIN(IF(1*ISNUMBER(1*MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)),ROW(INDIRECT("A1:A"&LEN(A1))),LEN(A1)+1)))*OR(ISNUMBER(1*LEFT(A1,1)),ISNUMBER(1*RIGHT(A1,1)))

This is an array formula so you must enter it with CTRL SHIFT ENTER rather than just ENTER.

Position Of First Non-Digit In A String

This formula will return the position of the first non-numeric character in the string in cell A1.

=IF(LEN(A1)=0,0,MIN(IF(1*ISNUMBER(1*MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))=0,ROW(INDIRECT("A1:A"&LEN(A1))),LEN(A1)+1)))*(ISNUMBER(A1)=FALSE)

This is an array formula so you must enter it with CTRL SHIFT ENTER rather than just ENTER.

Position Of The Last Occurrence Of A Character In A String

The following formula will return the postion of the last occurrence of the character in cell B1
in the string in cell A1.

=MAX((MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)=B1)*ROW(INDIRECT("A1:A"&LEN(A1))))

This formula does not distinguish between upper and lower case. If you want to make this distinction, use the formula

=MAX((EXACT(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1),B1))*ROW(INDIRECT("A1:A"&LEN(A1))))

This is an array formula, so you must press CTRL SHIFT ENTER rather than just ENTER.
If cell B1 is empty, the result is 0.

Counting The Number Of Words In A Cell

The following formula will return the number of words in a cell. A word is considered by be a string of characters delimited by
spaces. Other punctuation characters are not considered.

=IF(LEN(TRIM(A1))=0,0, LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1)

String Concatenation

You can combine two string into a single string by using either the CONCATENATE function or the & operator. Unfortunately, neither of these can be used in an array formula to selectively build up a result string based on other criteria. See String Concatenation For Array Formulas for a VBA function that can be used in an array formula to build a string based on selection criteria.

This page last updated: 2-November-2007