Split And Join Substitute And Perform Surgery On Text Strings
Convert text to lower case
Example: =LOWER("hello")
Result: hello
Convert text to upper case
Example: =UPPER(D3)
Result: JAMES
Convert text to proper case (each word’s first letter capitalized)
Example: =PROPER("this is a long sentence")
Result: This Is A Long Sentence
Combine different text values to one text
Example: =CONCATENATE(A3, " and ", A4)
Result: 30 and 25
Combine different text values to one text
Example: =A3&" and "&A4
Result: 30 and 25
Extract first few letters from a text
Example: =LEFT("Australia",3)
Result: Aus
Extract last few letters from a text
Example: =RIGHT("New Zealand",4)
Result: land
Extract middle portion from given text
Example: =MID("United States",4,5)
Result: ted S
What is the length of given text value
Example: =LEN("Titan")
Result: 5
Substitute one word with another
Example: =SUBSTITUTE("Microsoft Excel","cel","cellent")
Result: Microsoft Excellent
Replace some letters with other
Example: =REPLACE("XYZ123",4,3,"456")
Result: XYZ456
Find if a text has another text
Example: =FIND("soft","Microsoft Excel")
Result: 6
Extract initials from a name
A1 contains Sue Flay
Example: =LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)
Result: SF
Find out how many words are in a sentence
A1 contains “This is a very long sentence with lots of words”
Example: =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1
Result: 10
Remove unnecessary spaces from a cell
Example: =TRIM(" why so serious ")
Result why so serious
Remove anything after a symbol or word
A1 contains Excel, Titan
Example: =LEFT(A1,FIND(",",A1)-1)
Result: Excel