Importing Data into Excel: Fixing Text and Numbers

Quick reference

• 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.