- HD
- 720p
- 540p
- 360p
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
How to recognize when text and numbers imported incorrectly and how to use TRIM and FIND and REPLACE to fix errors.
Exercise files
Download this lesson’s exercise file.
Importing data into Excel: fixing text and numbers14.2 KB
Quick reference
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
- Headings should be in one row only
- No merged cells
- If a header is long, try to wrap text instead of merge cells
Text imports
- 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 &
Number imports
- 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
- 00:04 We often work with Excel sheets that contain data from other sources, PDFs, or
- 00:09 if you download a bank statement from the Internet or
- 00:12 report from an app like QuickBooks, where you might end up with a CSV file.
- 00:17 It could be that somebody sent you an Excel sheet that you must work with.
- 00:21 Very often data on these sheets can be bad, and
- 00:24 bad data causes incorrect results in a formula, and
- 00:27 results in functions that don't perform like you expect them to.
- 00:32 So let's look at what bad data can look like and look at a few quick fixes.
- 00:37 Remember, neither the fixes nor the data errors are extensive lists.
- 00:41 And in Excel there are usually many ways to fix something.
- 00:45 In this example, data is in a table like format.
- 00:49 That means that we have headers with data listed below.
- 00:52 As far as headers go,
- 00:54 data headers should only be in one row with no merging of columns.
- 00:59 Excel recognizes only that headers in the row are just above the data.
- 01:04 Sometimes you do have longer descriptions or headers that you must see, but
- 01:08 put the heading into one cell and then use the wrap text function.
- 01:13 One cell needs to look like this, Detailed description.
- 01:18 I'm going to deselect the merge option.
- 01:23 Now my Detailed description is only in cell B4, and
- 01:26 you can get rid of Detailed above.
- 01:30 Now the end of description spills into column C, which I don't want.
- 01:35 So I'm going to wrap text.
- 01:38 It's going to make my row nice and big so
- 01:40 that the Detailed description text fits only into column B.
- 01:45 There you go.
- 01:46 It may not be pretty, but that works.
- 01:48 So you get two types of data in Excel, you get numbers, that includes dates,
- 01:53 and you get text.
- 01:55 So if you add a number, it's automatically aligned to the right.
- 01:59 And if you add text, it's automatically aligned to the left.
- 02:03 You can force the alignment any way you want.
- 02:05 It doesn't change the way Excel sees the data.
- 02:08 But remember that the auto alignment right in the beginning is an indication of
- 02:13 what Excel sees an entry as, right aligned for numbers, left aligned for text.
- 02:19 If we look at this example and start in the Amount column,
- 02:23 you'll see that all the amounts are aligned to the left.
- 02:26 Which makes you think that Excel might see this data as text and not as numbers.
- 02:31 And that's confirmed if you add a SUM to the bottom of the column,
- 02:35 the SUM function looks correct, but if you press enter, it gives you no result.
- 02:40 The probable cause of this error is the dollar sign before each number in
- 02:44 the column.
- 02:45 So if I remove the dollar sign, Excel might see them as numbers.
- 02:49 The easiest way to remove all the dollar signs at once is to do a Find and Replace.
- 02:54 We can find all the dollar signs and
- 02:56 replace them with nothing to strip them out.
- 03:00 So let's hit ctrl+F to find, we'll type in the dollar sign,
- 03:05 and then replace that dollar sign with nothing.
- 03:10 We click Replace All.
- 03:12 And there you have all the numbers automatically aligned to the right very
- 03:16 quickly.
- 03:18 And now even the sum function works.
- 03:20 The next problem you might have is with imported text.
- 03:23 And the problem you usually encounter is that imported text often contains
- 03:28 extra spaces.
- 03:29 You don't necessarily see them but they can be there.
- 03:32 because if I'm looking for just the word MERCHANT but
- 03:35 Excel sees MERCHANT space, space, then the two won't agree, and
- 03:39 you'll end up with incorrect answers to functions.
- 03:43 To get rid of those spaces, I use the TRIM function, and this is what we do.
- 03:48 I'm going to use the cells on the side until I'm sure all my information is fixed
- 03:51 and correct.
- 03:52 And then I'll copy that over to column B.
- 03:55 trim(B5), and its MERCHANT.
- 04:00 And I can copy this down to fix all my MERCHANTS.
- 04:03 And I can copy it across to fix my CAPTURE and my DEPOSITS.
- 04:09 And there we go.
- 04:12 But the other problem with this text is that the text spilt over three columns
- 04:17 is actually supposed to be in just one column.
- 04:20 To get these three clean sets of text back into one cell,
- 04:23 there are a couple ways to do it.
- 04:26 One is to use the concatenate function.
- 04:31 There it is, concatenate.
- 04:32 If you can remember how to spell that, wonderful, but
- 04:36 a nice alternative is to simply type in an ampersand.
- 04:40 Much shorter, right?
- 04:41 Well, I'll show you how this works.
- 04:44 I'm going to take whatever is in this cell, and
- 04:47 I'm going to combine it with the ampersand, with a space.
- 04:51 Remember, we've taken all the spaces out of the cells, but
- 04:53 now we need to put spaces in between the words.
- 04:56 So I'm going to add the next word, and then another space, and
- 05:01 then the last word.
- 05:04 And then let's hit enter.
- 05:06 And there we have a beautiful MERCHANT CAPTURE DEPOSIT.
- 05:10 And I can copy this down to the other cells below.
- 05:15 And now that that this data looks perfect, I want to copy this and
- 05:19 paste it over here in my Detailed description column.
- 05:23 But before we do that, in order for this to work, I need to do a paste special.
- 05:30 A paste special to paste only the values.
- 05:33 I don't want to copy the formulas in those cells because there's no use for them.
- 05:37 I just want the values.
- 05:39 So we do paste special, values only, and I have my MERCHANT CAPTURE DEPOSIT.
- 05:45 We can expand to see it's all there, and now we can get rid of these two columns..
- 05:51 But now you see the REF error messages over here.
- 05:55 It's because I deleted those columns the cells reffered to.
- 05:58 But the good news is I don't need these anymore.
- 06:01 So let's delete all those columns.
- 06:04 And now I'm left with my fixed text and Amount data.
- 06:09 Now in the next video, I'd like to show you how to fix the date.
- 06:12 And then the one after that,
- 06:14 I'll show you how to get rid of any blank lines in your data.
- 06:17 See you in the next video.
Lesson notes are only available for subscribers.