Facebooktwitterlinkedin

Let’s geek out for a moment.  On some database talk.

Oh yeah, databases.  As an Infusionsoft Certified Partner, we have at least one complicated database job a week.

In some cases, an export from one application into another application is quite simple.

An example here, is exporting a list from Aweber or Mailchimp. Then doing a quick cleanup/tag job on it.  Then importing it into Infusionsoft  — and voila!  New database is in working order.

However, most cases come with complexity.  We tend to compile a full database by pulling records from various sources.

Example here:

  1. Source One: Constant Contact
  2. Source Two: Master Spreadsheet with Billing Details
  3. Source Three: Quickbooks with mailing addresses

Ouch!

We only want to import the constant contact followers, but we need to compile all billing related items, plus the mailing addresses, and in this client’s case they’re scattered.

What becomes real complicated is how the addresses in Quickbooks export….with no email address associated, might I add!

Double ouch!

The mailing addresses export with a line break in them:

How to Remove Line Breaks in Excel for Database Jobs

EXHIBIT A: What We Get from QuickBooks Address Export

Yuck! Sure, you can manually remove that line break from each row with a click and backspace, but when you have thousands of record lines, that is not going to be doable!

Our goal is a database that is ready for import, and it needs to look like this:

How to Remove Line Breaks in Excel for Database Jobs

EXHIBIT B: Our Goal

So out comes the handy resource commonly known as Google.

I found formulas that would do this for me.  There were 3 of them.  Each of them performed something close, but not quite nailing what I needed.

This happens in phases.  First phase is to get that line break removed.  The second phase is to use the “Text-to-columns” feature in Excel to reach our goal as EXHIBIT B shows.

How to get Line Breaks Removed from Excel Database

EXHIBIT C: Text-To-Columns Feature In Excel

Our initial formula results, from searches online, gave us one of these two scenarios.  They were close, but not quite!

2017-01-15_13-18-37

SCENARIO 1: One Line, but no comma between address line, and city.

SCENARIO 1 above puts us on one line, but we need our comma!

How to remove line breaks from an excel database.

SCENARIO 2: One Line, but no space between address line, and city.

SCENARIO 2 above puts us on one line, but we need our space!

The trick is when using the Text-To-Columns feature in Excel, we either have to choose to break our columns at spaces, or at commas. So we need to ensure we have the spacing or commas set correctly, or we end up with too many cells in our address lines, which does us no good for import.

how to remove line breaks in an excel database

EXHIBIT D: All spaces separated into columns.

So I played with the formula until I got it just right.  It took me about an hour to get this entire research done, but in the end of it all, we saved about 2-weeks in the database development process.  Woohoo!

So the formula you use to fix these blasted line breaks from an export?

=SUBSTITUTE(SUBSTITUTE(A4, CHAR(13),”, “),CHAR(10),””)

TIP: Watch your copy/paste job on that formula.  It may not paste the ” correctly.  If you get an error, back the ” out and type them.

Here’s what we got.  Yes! Exactly what I needed!

how to remove line breaks in an excel document

EXHIBIT D: The Correct Version

From here, you copy and paste that data into a new column using the PASTE SPECIAL feature as VALUES ONLY into a new cell.  You want to remove that formula junk.

Then you use the text-to-columns feature breaking at the comma, and VOILA!  You have clean columns.

Well….I say “clean” – but there’s always some database housekeeping and cleanup to do.  Especially in the international fields.

Happy database’g to you!

I’m done with my geek dump now.

 

 

 

Facebooktwitterlinkedin
Let's Be Friends!

Let's Be Friends!

Sign up to be notified of new posts to our tips and resources, and to catch our monthly newsletter.

Got it! Check your inbox for updates.

WordPress SEO