Use the TRIM function to return a cell's content without unwanted leading and trailing spaces. Leading spaces cause incorrect sorting results; other extra spaces prevent correct results when using lookup and matching functions. Trim also converts multiple consecutive inner spaces to a single space.
- [Voiceover] If your data has leading spaces in it,…sometimes it causes big problems.…You might not see any problem with this list here,…columns A through G.…If we scroll up and down, we eventually might run into…something that looks a little odd…like what we're seeing here, but we don't always…catch that, and maybe there are only a couple…of occurrences of that kind of an error, a leading space,…but we might have hundreds of these in a huge list…and we don't see them so easily.…Let's sort the data, not that we're necessarily…looking for these, but let's say that we do want this list…alphabetically sorted by employee name.…Since I know the data well…and it's all in one solid cluster, there are no empty rows…within this data, I can click on any cell in column A,…and then on the data tab in the ribbon, simply click…the AZ button, and the single AZ button…will sort this list by employee name.…
But it's pretty apparent we get some…unexpected results here.…These names here have leading spaces,…it looks like the first two have two spaces,…
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: Remove extra spaces with TRIM