Text Values Strings Words And Phrases

1 minute read

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

Example: =LEFT("India",3)
Result: Ind

Example: =RIGHT("New Zealand",4)
Result: land

Example: =MID("United States",4,5)
Result: ted S

What is the length of given text value

Example: =LEN("Chandoo.org")
Result: 11

Example: =SUBSTITUTE("Microsoft Excel","cel","cellent")
Result: Microsoft Excellent

Replace some letters with other

Example: =REPLACE("abc@email.com",5,1,"g")
Result: abc @gmail.com

Find if a text has another text

Example: =FIND("soft","Microsoft Excel")
Result: 6

H1 contains Bill Jelen
Example: =LEFT(H1,1)&MID(H1,FIND(" ",H1)+1,1)
Result: BJ

Find out how many words are in a sentence

H2 contains “This is a very long sentence with lots of words”
Example: =LEN(H2)-LEN(SUBSTITUTE(H2," ",""))+1
Result: 10

Example: =TRIM(" chandoo. org ")
Result chandoo. org

H3 contains someone@ something.com
Example: =LEFT(H3,FIND("@",H3)-1)
Result: someone

Categories: formulas

Updated: October 05, 2018

Twitter Facebook LinkedIn

Leave a Comment