Troubleshooting Text Files Imported into Excel

Excel

Importing data into Excel can be extremely easy or it can be so frustrating you have to leave your desk and walk around outside while muttering to yourself as your colleagues worry about your mental health. To keep your sanity, check out these tips.

Dates that won’t reformat

Make sure the date format used in the export file is a date format recognized by Excel. Use the same formats as those shown in Excel’s Date Format dialog box. You might need to also use some of the tips below.

Numbers that won’t calculate or format

Some computer programs, especially older banking programs, export numbers as text. They look like numbers in Excel, but they are treated as text. They won’t align like numbers, they may or may not calculate as numbers, and a PivotTable will treat them as text.

Convert these imposters into real numbers using the VALUE() function. For example, VALUE(B2) will convert the text-number in B2 into a numeric value that can be formatted and calculated. You may have to do this all the way down a column. Read on about TRIM() and CLEAN() for more gotchas.

Data with unwanted and invisible text spaces

Some report writers add extra text spaces to field names and even to data. Because blank spaces are hard to see this is almost as frustrating as numbers masquerading as text. To see if this is happening select an errant cell and click in the formula bar. Move through the text or number in the formula bar to see if there are unwanted spaces. For example, extra spaces after a number will not show in the cell, but will prevent the number from calculating.

Remove unwanted spaces from text or numbers by trimming them with the TRIM() function. For example, TRIM(B2) will remove unwanted spaces before or after the number in B2. You may have to create a column of TRIM() just to clean a column of data. And speaking of CLEAN()…

Removing Special Non-printable Characters

Once in an odd while, or with an antique report writer creating a PRN file, the data may contain hidden non-printing characters. These special characters do things such as create line breaks in a printed report. You will usually see them as a square character  in the formula bar.

Remove special character with the CLEAN() function. Like before you may need a column of CLEAN() functions to clean a column of data.

Converting, Trimming, and Cleaning Errant Data in one Swell Foop

If you have any doubt about the data you’re bringing in, or you’ve had trouble getting an occasional imported number to work, then just clean the whole darn column of data.

In an adjacent column place this formula and copy it down the length of the imported data column. It will CLEAN, TRIM, and change text-numbers into VALUEs in a single formula. If your data is in cell B2 create an adjacent column with the formula

=VALUE(TRIM(CLEAN(A2)))

Now copy the formula down. This combination of functions will remove nonprinting characters, remove unneeded spaces, and convert text-numbers into values.

Extracting Text or Removing Unwanted Text

If you’ve imported a string of text, for example, a city-state combination, you may end up with text in cell B2 that looks like,

Bend, OR

To extract the state, OR, from this, use RIGHT(B2,2) to extract the rightmost two characters.

Powerful text manipulation functions you can use to pull out any piece of text from a longer piece of text are:

  • LEFT()
    Pulls out the leftmost characters
  • RIGHT()
    Pulls out the rightmost characters
  • MID()
    Pulls out characters from the middle of a string of text
  • FIND()
    Finds the position of a character inside a string of text. Use this to find the position in a text string where you want to begin extracting characters.
  • LEN()
    Find2 the length of a string of text
  • &
    Concatenate (join together) two strings of text

Using these functions you can create formulas that can extract, combine, or manipulate any text you import. But that’s a topic for another time.

 

Share the power...