Use the functions LEFT and RIGHT to extract a selected number of characters either from the left side of a cell or the right side of a cell. A common application is using the RIGHT function to retrieve the five characters (Zip Code) from the right side of a cell containing a city, state and zip code.
- [Voiceover] Sometimes, when you want…to extract data from a cell, you want to extract it…from the right side or the left side.…Excel has two functions with those names.…We're in a worksheet called LEFT-RIGHT,…and in column A we've got some part numbers.…Imagine if the three left-most characters there…represent the color of the item, the size of it,…when it was made, where it was made, that sort of thing.…Let's simply extract the three left characters…from cell A2, for starters, right to here in cell B2.…#left(,3 Here's the data we're looking at.…
We want to extract three characters from the left-hand side,…and what we're about to see is 9C0…as I press Control + Return,…because I want to copy it down the column,…and Double-click, and there we see it.…I'll press Control + U…to re-expose what the function is doing,…and we see it there.…So, in all cases we're picking up three left-most characters…from the data in column A.…At other times, of course, we might want…to extract data from the right-hand side.…I think you can see in the data in column C…
Author
Dennis Taylor
Released
5/26/2016- Understanding how the hierarchy of operators affects formula results
- Knowing when to use absolute vs. relative references
- Using Formula Builder for unfamiliar functions
- Displaying a worksheet's formulas and highlighting formula cells
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding nested IF functions; using AND, OR, and NOT with IF
- Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX
- Analyzing data with the statistical functions: MEDIAN, MODE, etc.
- Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets
Skill Level Intermediate
Duration
Views
-
Introduction
-
Welcome50s
-
Exercise files25s
-
-
1. Formula and Function Tools
-
2. Formula and Function Tips and Shortcuts
-
3. IF and Related Functions
-
4. Lookup and Reference Functions
-
Use MATCH and INDEX together4m 40s
-
5. Statistical Functions
-
6. Power Functions
-
7. Selected Financial Functions
-
8. Math Functions
-
9. Date and Time Functions
-
10. Text Functions
-
11. Array Formulas and Functions
-
12. Reference Functions
-
13. Information Functions
-
Conclusion
-
Next steps25s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Extract specific data with LEFT and RIGHT