This workbook contains examples of advanced text formulas:
1) Counting characters in a cell case-sensitively or not;
2) Counting occurrences of a substring;
3) Creating ordinal numbers like 1st, 3rd, 45th;
4) Extracting parts of strings like file names from paths, first/last words, and names.
This workbook contains examples of advanced text formulas:
1) Counting characters in a cell case-sensitively or not;
2) Counting occurrences of a substring;
3) Creating ordinal numbers like 1st, 3rd, 45th;
4) Extracting parts of strings like file names from paths, first/last words, and names.
This workbook contains examples of advanced text formulas:
1) Counting characters in a cell case-sensitively or not;
2) Counting occurrences of a substring;
3) Creating ordinal numbers like 1st, 3rd, 45th;
4) Extracting parts of strings like file names from paths, first/last words, and names.
This workbook contains examples of advanced text formulas:
1) Counting characters in a cell case-sensitively or not;
2) Counting occurrences of a substring;
3) Creating ordinal numbers like 1st, 3rd, 45th;
4) Extracting parts of strings like file names from paths, first/last words, and names.
Download as XLSX, PDF, TXT or read online from Scribd
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