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 can be used to count the number of times that the character or string of characters in cell B1 occurs in the string in cell A1. For example, if A1 contains the string abcXdXeXf, and cell B1 contains the character x, the formula will return 3, since there are 3 'x' characters in A1. This formula does not distinguish between upper and lower case. Cell B1 may contain multiple characters. In this case, the formula counts the number of times that string occurs in cellA1, not the number of times each individual characters in cell B1 occur. For example, if B1 contains abc, the formula counts the number of times the string abc occurs in cell A1, not the number of times the individual characters a, b, and c occur in cell A1.

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

If B1 is empty, the formula returns 0. If you want to distinguish between upper and lower case, remove the UPPER functions from the formula. That is,

=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

Created by Chip Pearson at Pearson Software Consulting, LLC
Email: chip@cpearson.com Before emailing me, please read this page. 
http://cpearson.com/excel/stringformulas.aspx
Copyright © 1997 - 2009, Charles H. Pearson

Submit bug information or errors on the Bug And Error Report Page.



About This Web Site Activate The Main Excel Window Adding Dates Adding The COM Add-Ins Menu Item Adding the COM Add-Ins menu item Alerter Display Library Alerter Message Display Component Analysis ToolPak Application Events Array Formulas Array Formulas Arrays In VBA Arrays To Columns Attachments In Newsgroups Automation Add-Ins Blinking Text Blinking Text Bracket Pricing Browse Folder CALL Function Call Stack Calling Worksheet Functions Cell Referencing Cell View Add-In Class Modules Clipboard Functions Clone A Folder Code Modules Code Notes Collections And Dictionaries Color Band Formatting Colors And Functions COM Add-Ins In VB6 Conditional Formatting Conditional Formatting Colors Connected And Disconnected Objects Consulting And Development Services Creating An Add-In Date And Time Quick Entry Date And Time VBA Functions Date And Time Worksheet Functions Date And Times In Excel Date Arithmetic Date Intervals DATEDIF Function Dates And Times Daylight Savings Time Debugging VBA Declaring Variables Default Property Of A Class Deleting Duplicates Deleting Duplicates With Filter Detecting Scrolling Direct Connect The MSNEWS Directory Tree Listings Distributed Dates Document Properties Downloadable Files Downloadable Zip Files Downloads Duplicate Data In Lists And Ranges Duplicates In Lists Email Me Emailing ME Empty A Folder Enum Data Types Error Handling In VBA Error Values And The FormatMessage API EULA Answers Event Procedures Events In UserForms Excel Macros Excel Pages Excel Shutdown Excel Tools Excel Worksheet Functions Feet And Inches File Name Of COM Add-In File Times Find In Range First And Last Names Folder Tree Listing Folder Tree Listing Form Positioner Formula Bar Shortcut Fractional Arithmetic Functions And Subs Games Get And Set Properties GetInfo Function Headers And Footers Hidden Namespace Holidays Import And Export Text Importing Large Files To Excel Importing Large Text Files INDIRECT Function Insert And Fill A Series Insert Procedure Names In VBA Intro To VBA Julian Dates Keyboard Shortcuts Keyboard Tests Last Time Of File Latitude And Longitude Legalese Legalese and Copyright Information Links List Lookups ListBox Utlities Lists Of Data In Excel Lists On Worksheets Local And GMT Times Main Page Main Page Make Mulitple Levels Of Directories Menu Items Menus In The VBA Editor Modifying The Registry Multi File Import In VBA Multiple Monitors And Windows Name Box Shortcut Named Ranges Nested Functions New Posters Guide For Newsgroups No Blanks No Dupicate Entries Office Application Window Classes Optimize Your VBA Code Ordinal Number Suffixes Overtime And Timesheets Parsing Phone Numbers Passing And Returning Arrays In VBA Passwords Pivot Tables Popup Box Privacy Statement Progress Bar Random Numbers Ranking Data Recursive Programming Recycle A File Or Folder References Registry Related Pages Relative And Absolute References Relative And Absolute References Resources For Developers Returning Arrays Rounding In Excel Rowliner Add-In Save Copy And Zip A Workbook Save Coy And Zip Add-In Scheduling Procedures With OnTime Search The Site Search The Site Series Information SetParent And Userforms Sheet Referencing Shell And Wait Shortcut Keys Shortcut Keys For VBA Show Any Form Site Feedback Size String Some Friendly Sort And Array In VBA Sort By Color Sort Worksheet Tabs Sorting Arrays Of Objects Sorting Objects Comparison Special Characters Special User Folders Startup Errors In Excel Status Bar In Excel Subclassing Windows Support Suppress Changes In Forms System Error Messages From Numbers Temporary Files And Folders Test Friendly Text Box Validation Time Zones Timed Close Topic Index Truly Global Variables Unselect A Cell Or Area Upper And Lower Case User Defined Functions User Defined Functions Validate A Text Box Values And Formulas Variables In VBA VBA Code To Modify VBA Code Versions Of Excel Wait For File Close Week Numbers What's New On The Site Widen The Name Box Word Count Of Worksheet Zomming And Centering  
0 - Rate This Page 1 - Not Helpful 2 - Somewhat Helpful 3 - Quite Helpful 4 - Very Helpful 5 - Exactly What I Needed

Essential Tools For Developers




Essential Tools For Financial Analysts And Accounting Professionals

  
Ready


Advertise Your Product On This Site