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

 AB
1Russell Proctor200

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


2) Determining if two strings are identical

 AB
1Better Solutionsbetter solutions
2BE MORE PRODUCTIVEBE MORE PRODUCTIVE

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


3) Obtaining the first word from a string

 AB
1Russell ProctorMicrosoft 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)

 AB
1Russell ProctorMicrosoft 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)

 AB
1Russell ProctorBetter 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

 AB
1Russell ProctorBetter 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

 AB
1bettersolutions.combe more productive

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


8) Changing the text to lowercase

 AB
1BETTERSOLUTIONS.COMBE MORE PRODUCTIVE

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


9) Changing the first letter of every word to uppercase

 AB
1microsoft office developmentbetter SOLUTIONS

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


10) Obtaining everything to the right of a dash

 AB
1Russell - ProctorBetter - Development

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


11) Obtaining everything to the left of a dash

 AB
1Russell - ProctorBetter - Development

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


12) Repeating a character or string a number of times

 AB
1better-@

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


13) Removing the extra spaces at the front

 AB
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)

 AB
1Microsoft Office DevelopmentExcel 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)

 AB
1MicrosoftExcel 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"



© 2018 Better Solutions Limited. All Rights Reserved. © 2018 Better Solutions Limited TopPrevNext