Text Formulas

Formula Summary

1) Determining if a cell contains text
2) Determining if two strings are identical
3) Obtaining the first word from a string
4) Obtaining the last word from a string (when there are 2 words)
5) Obtaining the initials (or first characters of each word)
6) Changing the order of the 2 words
7) Changing the text to uppercase
8) Changing the text to lowercase
9) Changing the first letter of every word to uppercase
10) Obtaining everything to the right of a dash
11) Obtaining everything to the left of a dash
12) Repeating a character or string a number of times
13) Removing the extra spaces at the front
14) Obtaining the last word from a string (when there are at least 2 words)
15) Obtaining the last word from a string (when there may only be one word)

1) Determining if a cell contains text

	A	B
1	Russell Proctor	200

=ISTEXT(A1)=TRUE
=ISTEXT(B1)=FALSE

2) Determining if two strings are identical

	A	B
1	Better Solutions	better solutions
2	BE MORE PRODUCTIVE	BE MORE PRODUCTIVE

=EXACT(A1,B1)=FALSE
=A1=B1=TRUE
=EXACT(A2,B2)=TRUE

3) Obtaining the first word from a string

	A	B
1	Russell Proctor	Microsoft Office Development

=LEFT(A1,FIND(" ",A1))="Russell"
=LEFT(B1,FIND(" ",B1))="Microsoft"

4) Obtaining the last word from a string (when there are 2 words)

	A	B
1	Russell Proctor	Microsoft Office Development

=MID(A1,FIND(" ",A1,1)+1,LEN(A1))="Proctor"
=MID(B1,FIND(" ",B1,1)+1,LEN(B1))="Office Development"

5) Obtaining the initials (or first characters of each word)

	A	B
1	Russell Proctor	Better Solutions

=LEFT(A1)&MID(A1,FIND(" ",A1)+1,1)+"RP"
=LEFT(B1)&MID(B1,FIND(" ",B1)+1,1)+"BS"

6) Changing the order of the 2 words

	A	B
1	Russell Proctor	Better Solutions

=MID(A1,FIND(" ",A1,1)+1,LEN(A1))&" "&LEFT(A1,FIND(" ",A1))="Proctor Russell"
=MID(B1,FIND(" ",B1,1)+1,LEN(B1))&" "&LEFT(B1,FIND(" ",B1))="Solutions Better"

7) Changing the text to uppercase

	A	B
1	bettersolutions.com	be more productive

=UPPER(A1)="BETTERSOLUTIONS.COM"
=UPPER(B1)="BE MORE PRODUCTIVE"

8) Changing the text to lowercase

	A	B
1	BETTERSOLUTIONS.COM	BE MORE PRODUCTIVE

=LOWER(A1)="bettersolutions.com"
=LOWER(B1)="be more productive"

9) Changing the first letter of every word to uppercase

	A	B
1	microsoft office development	better SOLUTIONS

=PROPER(A1)="Microsoft Office Development"
=PROPER(B1)="Better Solutions"

10) Obtaining everything to the right of a dash

	A	B
1	Russell - Proctor	Better - Development

=RIGHT(A1,LEN(A1)-FIND("-",A1))="Proctor"
=RIGHT(B1,LEN(B1)-FIND("-",B1))="Development"

11) Obtaining everything to the left of a dash

	A	B
1	Russell - Proctor	Better - Development

=LEFT(A1,FIND("-",A1)-1)="Russell"
=LEFT(B1,FIND("-",B1)-1)="Better"

12) Repeating a character or string a number of times

	A	B
1	better-	@

=REPT(A1,3)="better-better-better-"
=REPT(B1,5)="@@@@@"

13) Removing the extra spaces at the front

	A	B
1	Russell Proctor	BetterSolution.com

=TRIM(A1)="Russell Proctor"
=TRIM(B1)="BetterSolutions.com"

14) Obtaining the last word from a string (when there are at least 2 words)

	A	B
1	Microsoft Office Development	Excel Word PowerPoint

=RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))="Development"
=RIGHT(B1,LEN(B1)-FIND("#",SUBSTITUTE(B1," ","#",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))="PowerPoint"

15) Obtaining the last word from a string (when there may only be one word)

	A	B
1	Microsoft	Excel Word PowerPoint

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=0,A1,RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))="Microsoft"
=IF(LEN(B1)-LEN(SUBSTITUTE(B1," ",""))=0,B1,RIGHT(B1,LEN(B1)-FIND("#",SUBSTITUTE(B1," ","#",LEN(B1)-LEN(SUBSTITUTE(B1," ",""))))))="PowerPoint"