MY CARTVideo Lesson:
This video lesson, titled “How to Use Excel to Extract Address Elements from a List Exported from QuickBooks Desktop,” shows you how to use functions and formulas to extract address elements from a list exported from QuickBooks Desktop.
How to Use Excel to Extract Address Elements from a List Exported from QuickBooks Desktop:
This video shows you how to use different logical and text functions within nested formulas in Excel to extract address elements from a list exported from QuickBooks Desktop, like the Customers list, for example. QuickBooks Desktop stores addresses for its customers and vendors as billing and shipping lines you would see in a mailing address.
For example, in a customer record, the billing city, state, and zip, appear as one field of “Bill to” data. It doesn’t store the discreet elements of the mailing address, like city, state, and zip code. This makes it challenging to export the data from QuickBooks Desktop and then import it into other applications. This lesson shows you methods of data cleansing and different formulas you can use to help you extract this information.
Open the Exported QuickBooks List Data in Excel:
To start, first open an exported list with address information from QuickBooks Desktop within Excel. If needed, you can copy all the data from the exported list and then paste it as values within a new workbook by using paste special in Excel. You may need to do this because sometimes the list data exported as a new workbook from QuickBooks Desktop to Excel won’t calculate formulas created within it. For this example, we’ll look at using the “Customers” list exported from the “sample product-based business” sample file in QuickBooks Desktop Pro Plus.
Clean the Data to Ensure Data Consistency:
After opening the list data, then examine it for data consistency. Ideally, when you examine the data in Excel, the data will have identical data storage patterns. However, that is rarely the case. So, you’ll want to clean the data to ensure data entry consistency and make the data entry patterns as easy as possible for you to use.
For example, looking at our sample “Customers” list, we can see that the “City, State, and Zip” field of data appears either in the “Bill to 3” or “Bill to 4” data field. Its location changes depending on whether the full billing address contained both a company name and contact name or only a contact name. Additionally, there was inconsistent data entry in the “City, State, and Zip” field, as some values have a comma and space between the city and state, while others only have a space. Additionally, between the state and zip there are often either 1 or 2 space characters.
First, you’d want to fix these data entry inconsistencies. So, you can use the “Replace” feature in Excel to replace the inconsistent data in the columns, as needed. Using our example, we’d want to replace the comma and space value in the columns that contain “City, State, and Zip” info with a single space. Then replace the double space values in those same columns with a single space. Depending on how data entry was performed in your company, you may need to make several replacements in different columns to get the data entry consistency required for your formulas to function correctly. Ideally, you want the data entry as consistent as possible.
Insert New Columns for the Data Elements and Formulas:
After you have consistent data, you can then insert additional columns for the address elements to extract. Let’s look at how to extract the zip code, state, city, and street address from the data, now that it has a consistent structure, even though it is often in different fields in the export file. As with the data entry analysis, you want to look for a logical pattern for the data storage in the fields. In this file, the “City, State, and Zip” data is either in “Bill to 4” or it is in “Bill to 3.” If it is in “Bill to 3,” then “Bill to 4” is blank.
Using Logical and Text Functions in Formulas to Extract Data:
To help us locate the data and then act on it in Excel, we can use the IF function and a nested ISBLANK function in this case. The IF function tests a cell and produces one result if the test is true and another result if the test is false. The ISBLANK function in Excel returns a true or false value based on whether a cell is empty. Combining these together then lets us test whether a cell is empty, and then extract data from it, as needed.
The other thing to note about the pattern of data in the “City, State, and Zip” field is that the last 5 characters at its right end contain the zip code. Thankfully, there are no Zip-4 codes to deal with in this example. If there were, you would probably want to tackle that, first. Continuing to the left, you then have the space and the two-digit state abbreviation. After that, there is a space, and then the city name.
Looking at this data pattern, it becomes apparent that the easiest way to start segmenting this data field into its discreet address elements is by starting at the right end of the field, as it is more consistent. The city name at its left end may have one or more words and different lengths and is therefore harder to work with. So, let’s start by extracting the zip code into a new field in the list. Since the zip code is always the last 5 characters in the data field, we can use the RIGHT function in a formula combined with the IF and ISBLANK function to extract it.
How to Extract the Zip Code:
Assuming you are in row 2 of the worksheet and the “Bill to 3” field is column S and the “Bill to 4” field is column T, then click into one of the adjacent columns you inserted and where you want the zip code to appear. Then, to extract the zip code, type this formula into the field.
=IF(ISBLANK(T2),RIGHT(S2,5),RIGHT(T2,5))
This formula uses the IF function and a nested ISBLANK function to test if column T is blank. If the ISBLANK function returns a true value, then that means the “City, State, and Zip” data must be in column S. Therefore, it should then extract the right 5 characters from column S. Otherwise, if T is not blank, it means that the “City, State, and Zip” data is in column T, and it should extract the right 5 characters from column T, instead. Once you have the data appearing correctly, you can then fill the formula down the column to fill-in the rest of the zip code values for the other rows.
How to Extract the State Abbreviation:
Next, we’ll tackle extracting the state abbreviation from the “City, State, and Zip” fields. Now we know that the two-character state abbreviation code is one space away to the left from the zip code at the right end of this field. While it may be tempting to use the MID function here to try and extract the state code, the MID function works from a left-focused starting position, which then involves extra calculations involving the LEN function and subtraction in a situation like this where the left-most characters are variable in length. To help simplify this formula, we’ll just use the LEFT function in combination with the RIGHT function.
Once again, assuming you are in row 2 of the worksheet and the “Bill to 3” field is column S and the “Bill to 4” field is column T, then click into one of the adjacent, empty columns you inserted and where you want the state abbreviation to appear. Then, to extract the state abbreviation, type this formula into the field.
=IF(ISBLANK(T2),LEFT(RIGHT(S2,8),2),LEFT(RIGHT(T2,8),2))
This formula also uses the IF function and a nested ISBLANK function to test if column T is blank. If the ISBLANK function returns a true value, then that means the “City, State, and Zip” data must be in column S. Therefore, it should then extract the left 2 characters from the text string of the right 8 characters in column S. Otherwise, if T is not blank, it means that the “City, State, and Zip” data is in column T, and it should extract the left 2 characters from the string of the right 8 characters in column T, instead. Once you have the data appearing correctly, you can then fill the formula down the column to fill-in the rest of the state values for the other rows.
How to Extract the City:
Next, we’ll look at extracting the city name from the “City, State, and Zip” data field. Once again, it will probably be easiest to work from the right side instead of the left. This time, we’ll use the SUBSTITUTE function to replace the state and zip code string in the data field with nothing. This makes the SUBSTITUTE function delete the string instead of replacing it with data.
In this example, we use the SUBSTITUTE function instead of the REPLACE function, even though they work similarly, as it simplifies the string replacement formula. Like the MID function, the REPLACE function would need us to calculate the string length using the LEN function and then deal with subtraction, just to get the required arguments for the function.
Once again, assuming you are in row 2 of the worksheet and the “Bill to 3” field is column S and the “Bill to 4” field is column T, then click into one of the adjacent, empty columns you inserted and where you want the city to appear. Then, to extract the city, type this formula into the field.
=IF(ISBLANK(T2),SUBSTITUTE(S2,RIGHT(S2,8),””),SUBSTITUTE(T2,RIGHT(T2,8),””))
This formula also uses the IF function and a nested ISBLANK function to test if column T is blank. If the ISBLANK function returns a true value, then that means the “City, State, and Zip” data must be in column S. Therefore, it should then show the value from cell S2 and substitute the right 8 characters in that cell with nothing, as shown by the empty double quotes. Otherwise, if T is not blank, it means that the “City, State, and Zip” data is in column T, and it should instead show the value from cell T2 and substitute the right 8 characters in that cell with nothing, shown by the empty double quotes. Once you have the data appearing correctly, you can then fill the formula down the column to fill-in the rest of the city values for the other rows.
How to Extract the Street Address:
Finally, to extract the street address, we just need to know if column T is blank. If so, then the street address is in the “Bill to 2” field in column R. Otherwise, the street address is in the “Bill to 3” field in column S. Once again, assuming you are in row 2 of the worksheet and the “Bill to 2” field is in column R, the “Bill to 3” field is column S, and the “Bill to 4” field is column T, then click into one of the adjacent, empty columns you inserted and where you want the street address to appear. Then, to extract the street address, type this formula into the field.
=IF(ISBLANK(T2),R2,S2)
Once you have the data appearing correctly, you can then fill the formula down the column to fill-in the rest of the street address values for the other rows.
What about REGEXEXTRACT?
Hopefully this deep dive into how to use Excel to extract address elements from a list exported from QuickBooks Desktop helps you successfully export and import your QuickBooks Desktop list data for other programs, as needed. If you’re wondering why I didn’t choose to use a function like REGEXEXTRACT for some of the data, like the zip codes, it’s just because selecting the pattern argument for that function is more difficult for many users to understand.
Additionally, REGEXEXTRACT, while also very useful for data extraction, is more helpful if the pattern you want to extract is unique within a field or set of fields. However, in our sample file example, there were 2 records containing a P.O. Box number that was also exactly 5 numeric characters long. In this case, the numeric pattern REGEXEXTRACT uses cannot differentiate the P.O. Box number in the field from a zip code in the field if you use the REGEXEXTRACT function by itself. Unless the user is diligently double-checking their data, it’s easy to miss.
However, if you combine the REGEXEXTRACT function with the IF and ISBLANK functions, then it also works well to extract zip codes. One additional bonus of doing this is that it also can extract Zip+4 data from the field, which we didn’t need to deal with in our sample. Using the same sample data, you can use the REGEXEXTRACT function to extract zip codes and zip+4 codes, as follows.
=IF(ISBLANK(T2),REGEXEXTRACT(S2,”\b\d{5}(?:-\d{4})?\b”),REGEXEXTRACT(T2,”\b\d{5}(?:-\d{4})?\b”))
