# Quick Study Text Functions

## Extracte Text From String

Data Formula Result Description
12345-6789 `=LEFT(A2,5)` 12345 Convert nine-digit postal codes into five-digit postal codes
(888)123-4567 `=RIGHT(A3,8)` 123-4567 Extract phone numbers without the area code
P3X245 `=MID(A4,3,1)` X Extract the 3rd character of the string

## Join text strings

Text1 Text2 Text Result Description
Chris McMullen `=A2&" "&B2` Chris McMullen Use the ampersand (`&`) operator
Chris McMullen `=CONCATENATE(A3," ",B3)` Chris McMullen Combine text
Micro soft `=CONCAT(A4:B4)` Microsoft Combine range of cells
Chris McMullen `=TEXTJOIN(" ",TRUE,A5:B5)` Chris McMullen Combine text in an easier way

## Set text to sentence case

Data Formula Result Description
knowledge is power `=UPPER(A2)` KNOWLEDGE IS POWER Convert text to all uppercase
KNOWLEDGE is power `=LOWER(A3)` knowledge is power Convert text to all lowercase
knowledge is power `=PROPER(A4)` Knowledge Is Power Convert text to title case
knowledge is power `=UPPER(LEFT(A5,1))&RIGHT(A5,LEN(A5)-1)` Knowledge is power Convert text to sentence case

## Remove spaces from a text string

Data Formula Result Description
Excel 365 `=TRIM(A2)` Excel 365 Remove leading spaces
Excel   365 `=TRIM(A3)` Excel 365 Remove middle spaces
Excel 365 `=TRIM(A4)` Excel 365 Remove trailing spaces
Excel?365 `=TRIM(SUBSTITUTE(A5,CHAR(160)," "))` Excel 365 Remove nonbreaking space

## Find specific characters

Data Formula Result Description
AB-123-456 `=FIND("-",A2)` 3 Find first hyphen
AB-123-456 `=FIND("-",A3,FIND("-",A3)+1)` 7 Find second hyphen
AB-123-456 `=RIGHT(A4,LEN(A4)-FIND("-",A3,FIND("-",A3)+1))` 456 Extract everything after the second hyphen
AB-123-4567 `=RIGHT(A5,LEN(A5)-FIND("-",A4,FIND("-",A4)+1))` 4567 Extract everything after the second hyphen

## Substitute text strings

Data Formula Result Description
BEN'S COFFEE `=SUBSTITUTE(PROPER(SUBSTITUTE(A2,"'","zzzzz")),"zzzzz","'")` Ben's Coffee Fix the apostrophe S issue
A B C D `=LEN(A3)-LEN(SUBSTITUTE(A3," ",""))` 3 Count space character in a cell
Big Big World `=LEN(A4)-LEN(SUBSTITUTE(A4," ",""))+1` 3 Count words in a cell

## Add a line break in formula

Data Formula Result Description
Ben `=A2&CHAR(10)&2023` Ben
2023
Use line breaks in chart x-axis labels

* View this result in Format Cells > Alignment > Wrap text

## Format the numbers

Data Formula Result Description
50 `=TEXT(A2,"#"" lbs""")` 50 lbs Add any text
12345 `=TEXT(A3,"00000")` 12345 Zip Code
123456789 `=TEXT(A4,"00000-0000")` 12345-6789 Zip Code + 4
1234567899 `=TEXT(A5,"[<=9999999]###-####;(###) ###-####")` (123) 456-7899 Phone Number
123456789 `=TEXT(A6,"000-00-0000")` 123-45-6789 Social Security Number

=50&CHAR(12)&" Pt" `=CLEAN(A2)` 50 Pt Removes the first 32 nonprinting characters
="Excel"&"   2023"&CHAR(12) `=TRIM(CLEAN(A3))` Excel 2023 Removes the first 32 nonprinting characters and excess spaces