Text Formula Examples

Download as xlsx, pdf, or txt
Download as xlsx, pdf, or txt
You are on page 1of 2

This workbook contains the advanced text formula examples

Counting specific characters in a cell


Babba-booey 1 <-- case-sensitive
Babba-booey 4 <-- not case-sensitive

Counting the occurrences of a substring


Blonde on Blonde Blonde 2 <-- case-sensitive
Blonde on Blonde blonde 2 <-- not case-sensitive

Creating an ordinal number


1 1st
3 3rd
45 45th
122 122nd

Extracting a file name from a path


c:\windows\text.txt text.txt

Extracting the first word of a string


Do you use Excel? Do
Do <-- Excel 2007 only
Extracting the last word of a string
Do you use Excel? Excel? <-- Assumes at least one space
ExcelFormulas ExcelFormulas <-- Works if no spaces are present
ExcelFormulas ExcelFormulas <-- Excel 2007 only

Extracting all but the first word of a string


Do you use Excel? you use Excel?
Excel? Excel?
Excel? Excel? <-- Excel 2007 only
Extracting first name, middle names, last names
John Q. Public John Q. Public
Lisa Smith Lisa Smith
J. R. Robins J. R. Robins

Removing titles from names


Mrs. James Jones James Jones
Dr. Richard Speck Dr. Richard Speck
Mr. Ed Ed

Counting words in a cell


how many words? 3
134569 6
Microsoft Excel 2
Excel 1

You might also like