文本函数

This workbook contains the advanced text formula examples

Counting specific characters in a cell

Babba-booey1 <-- case-sensitive

Babba-booey4 <-- not case-sensitive

Counting the occurrences of a substring

Blonde on Blonde Blonde2 <-- case-sensitive

Blonde on Blonde blonde2 <-- not case-sensitive

Creating an ordinal number

11st

33rd

4545th

122122nd

Displaying a column letter

5E E

45AS AS

256IV IV

257IW

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

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

Extracting all but the first word of a string

Do you use Excel?you use Excel?

Extracting first name, middle names, last names

John Q. Public John Q.Public Q. <-- Alternate formula for middle name Lisa Smith Lisa Smith <-- Alternate formula for middle name J. R. Robins J.R.Robins R. <-- Alternate formula for middle name 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

1 3 4 5 6 96

Microsoft Excel2

Excel1

ula for middle name nate formula for middle name nate formula for middle name