About this lesson
Learn to break apart text with the LEFT, RIGHT, MID, FIND and SEARCH functions, and to combine text with the & character.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Text Functions.xlsx15.1 KB Text Functions  Completed.xlsx
15.1 KB
Quick reference
Topic
Breaking apart text in Excel via the use of the LEFT, RIGHT, MID, FIND and SEARCH functions, as well as putting it back together using the & character.
When to use
These formulae are extremely helpful when cleaning up data for other uses such as mail merges or lookups scenarios.
Instructions
Basic text extractions
=LEFT(“Rugby”,3) 
Returns “Rug” 
=RIGHT(“Football”,4) 
Returns “ball” 
=MID(“Bookkeeper”,3,4) 
Returns “okke” (Starts at 3^{rd} character and returns the next 4.) 
Putting text back together
=A1&B1 
Joins the text in A1 to the text in B1 (without adding any spaces) 
=”Foot”&”Ball” 
Returns “FootBall” 
Finding specific characters
=FIND(“transplant”,”Heart transplant”,1) 
Returns 7. (The position of the first letter of the word we searched for.) 
=FIND(“Transplant”,”Heart transplant”,1) 
Returns #N/A. FIND is does case sensitive searches, so Transplant is not the same as transplant. 
=SEARCH(“transplant”,”Heart transplant”,1) 
Returns 7. (The position of the first letter of the word we searched for.) 
=SEARCH(“Transplant”,”Heart transplant”,1) 
Returns 7. SEARCH does not perform case sensitive searches like the FIND function does, so Transplant is the same as transplant. 
Guarding against FIND/SEARCH errors
 Nesting your FIND or SEARCH in an IFERROR function will help guard against the #N/A values.
 =IFERROR(FIND(“transplant”,A1,1),”Not Found!”) will return “Not Found” if the FIND function can’t find “transplant” in A1.
Relative & Absolute Referencing

A relative reference adjusts when you copy the formula. E.g: The formula =A1 copied to cell B1 changes to =B1

An absolute reference never changes. E.g: The formula =$A$1 copied to cell B1 stays =$A$1
When entering formulas, you can specify an absolute cell by manually typing the $ sign; or with the formula selected and highlighted you can press F4.
Note: In addition to the ones covered here, there are many other text functions available in Excel.
Lesson notes are only available for subscribers.