About this lesson
How to recognize when text and numbers imported incorrectly and how to use TRIM and FIND and REPLACE to fix errors.
Download this lesson’s exercise file.Importing data into Excel: fixing text and numbers
Importing data into Excel: fixing text and numbers
Learn to identify and sort out issues with imported data so that it is useable in Excel.
When to use
Data often corrupts when imported into Excel - this lesson helps you identify possible issues and helps you fix them.
Instructions, Hints & Tips
Fixing text and numbers
Data often corrupts when imported into Excel.
Text imported into Excel will auto-align left and numbers and dates will auto-align to the right-hand side of a cell.
- Headings should be in one row only
- No merged cells
- If a header is long, try to wrap text instead of merge cells
- Be careful of imported text, it may seem ok visibly but may have imported spaces
- To get rid of the spaces, use the TRIM function
- To combine text, use CONCATENATE or &
- If numbers import incorrectly into Excel, it will not be included in calculations
- In this example the $ caused Excel to think the numbers are text
- To fix that, use a simple FIND and REPLACE
- Find the $ and replace with nothing
- If this worked, you notice the numbers auto aligning to the right-hand side of the cell
Login to download
Lesson notes are only available for subscribers.