Using Excel Flash Fill to Format Data for QuickBooks

by

In Person Events

Does everything always have to be about QuickBooks? I don’t think so. In today’s "Tuesday Tip," we'll look at a valuable, but perhaps not too widely known Excel feature called, “Flash Fill.”

Recently, I exported some data from a legacy accounting system. Boy was it a mess. There was no rhyme or reason to the data. The address field had numbers and names all mixed together, sometimes with spaces between them, and sometimes without.

If I'm going to import this data into QuickBooks, I have to get it into a format that's acceptable to the the address field. That even makes sense to the mailman.

Normally I might have to write a formula something like this (shown below) to try to break apart the combined numbers and letters within an individual cell. 

Just looking at that formula gives me a headache. It's a lot of work unless you do this Excel stuff full time. Even then, you might forget where to put a specific punctuation in the formula.

Now let's look at a much simpler way to achieve our goal. But first we must look at an excerpt of some of my messy data. In the spreadsheet example shown below, you'll see that the Street Address column is one big mess.

If we look just at Jane Doe’s address, we see that the cell (C2) has it listed as, 908Messenger Lane (spell checker is going nuts about now in my article format, even though Excel finds nothing wrong with the entered data).

Of course, we want, we need, the address to be post office friendly so we can send Jane some snail mail from time to time. 

What we really want is for her address to be 908 Messenger Lane. There must be a space between the 908 and the word Messenger.

The same applies to all of the addresses in column C. The old system’s export was seriously lacking.

To make our changes, I'll be using the Flash Fill feature located on the Data tab ribbon of Excel.

Alright, I know what you're thinking: “Murph, you only have six of these to do, so wouldn’t it be simple to just insert a space in the right place of column C?” 

You're absolutely right. If I only had the six I'm showing you, I'd be stupid to go through the steps I'm about to outline. But since I had a few hundred of these intermingled with ones that were correct, I think this is a pretty good method.

Flash Fill allows you to automatically fill data in a specific pattern across multiple columns.

In my example, I must first make room for the revised data by inserting two columns between the Street Address and City columns of my spreadsheet.

As you can see, I have named my new columns "Street Number and "Street." My intention is to use Flash Fill to assign the numeric portion of the address to the Street Number field, and to assign the rest of the address to the Street field.

As I experimented with Flash Fill, I found an approach slightly different than what Microsoft teaches in their tutorial worked best for me. But first let's look at the "Microsoft Way."

According to Microsoft, I should begin by putting my cursor in cell "2D." In other words, in the Street Number column to the right of the field containing 908Messenger Lane. Next, I should type the numbers 908.

I'd then move my cursor to cell "3D" and click the Flash Fill button on the Data Ribbon. When I do, Excel has learned the pattern and fills in the remaining fields of the Street Number column. There is just one problem –  I don’t get the expected results in all the fields (shown below). 

In Row 3, the entire address was copied to the Street Number. It worked correctly in Rows 4 and 5, but I received the same problem again with Row 6. The whole address went in there.

Microsoft would then tell you to take the same steps with Column E, the Street fields. In other words, type Messenger Lane in cell "2E," then, when you click the Flash Fill button with your cursor in cell "3E," the names should be copied over.

But in experimenting with this, I found that if you reverse the order you get far fewer errors when working with Flash Fill.

So, I want to begin with the Street Column (E). I will type Messenger Lane in cell "2E" (shown below).

When I move my cursor to Cell "3E," and click the Flash Fill button from the Data tab ribbon, I get all the right information. My street names are just the way I need them.

Now I move back to Cell "2D" and type in 908. When I move my cursor to Cell "3D" and click the Flash Fill button – “Perfection.”

As you can see, the street numbers have only the numbers. Apparently, Flash Fill learns with the more it does when the patterns remain consistent across the fields.

It is easy for me now to use the Excel Concatenate function to combine the Street Number, plus a space, plus my Street back together, so that I end up with just exactly the right address to import into a new accounting solution.

Like I said, we don’t always have to learn about QuickBooks or another General Ledger product, or even some third party app that works with one of the GLs. 

Excel is a great tool for accountants, bookkeepers and consultants, so we should learn about it too from time to time.

If you're not totally familiar with the huge number of features and functions Excel offers, you might want to consider taking a class or stay tuned to Insightful Accountant. We may just offer you another "Tuesday Tip" on Excel in the future.