fbpx
Data Cleaning in Excel 101, Part 5: Numbers That Don't Act Like Numbers and Leading Zeros - Inciter
65022
post-template-default,single,single-post,postid-65022,single-format-standard,ajax_fade,page_not_loaded,,select-child-theme-ver-1.0.8,select-theme-ver-1.7.1,smooth_scroll,wpb-js-composer js-comp-ver-6.3.0,vc_responsive

Data Cleaning in Excel 101, Part 5: Numbers That Don’t Act Like Numbers and Leading Zeros

Ever try to do a calculation with numbers in Excel and get an error or the numbers don’t seem to be adding up? Ever fight with ZIP code formatting? Below you can find some methods of dealing with numbers that just aren’t acting like numbers. We’ll also review some instances in which you’ll actually want Excel to store numbers as text, and how to convert them.

This is Part 5 in our Data Cleaning in Excel 101 series. Part 1 showed methods of removing blank rows from your data. Part 2 focuses on splitting data from one cell to multiple cells. Part 3 covers the opposite: combining data from multiple columns into one column. In Part 4, we discussed some incredibly useful and time-saving tricks with Excel’s “Flash Fill” function.

Numbers that don’t act like numbers

Spreadsheets are all about storing numerical data and making calculations. But what do you do if numbers aren’t acting like numbers and Excel is preventing you from doing even basic calculations?

example of SUM formula not working for two numbers
This happens when numbers are stored as text. A good way to tell that Excel is storing numbers as text is checking how the numbers are aligned. Excel left-aligns text and right-aligns numbers. Often, the error is a result of an export from another system that doesn’t encode the numbers correctly. There are a few ways to fix this issue.

Method 1: Convert to Number

yellow triangle error message
  1. Notice the little green triangle at the top left corner of the cell. When you select the cell, it will show you a yellow triangle warning.
Error options after click

2. When you click the yellow triangle warning, the specific error or warning message will be shown. In this case, “Number Stored as Text”.

results of convert to number

3. If you select the “Convert to Number” option, it will change the format of the cell and allow math to work on the number.

highlight to convert multiple

4. Uh oh… 5 + 25 does not equal 5, but you don’t have to convert each cell separately. You can highlight multiple cells with the green triangle and convert them at the same time.

all numbers converted correctly

5. Okay, that looks like it’s working!

There’s one additional trick with this method. You won’t see the error option to Convert to Number on cells that are already numbers. So you always have to start your highlighting from a cell that has the error, but then you can Convert to Number, and it will not alter cells that are already numbers.

Method 2: Convert with basic arithmetic (+, -, *, /)

In some cases you may have a large data set but only some of the numbers are stored as text. You don’t want to hunt through each row to look for the ones that need to be converted. To convert them quickly, you can use basic arithmetic operations like addition, subtraction, multiplication, and division which automatically convert cells to numbers if they are not already numbers.

The great thing about this method is that it works faster than the Convert to Number and you don’t have to find the little green triangles which are good at hiding in large data sets.

using +0 and *1 to convert text to number

1. In this case you can use +0 or *1 to make the conversion.

copy and paste as values menu

2. If you’d like to replace the original values that were causing you issues, then you can highlight the new numbers, copy them and use paste special as values on top of the originals.

Method 3: Remove leading or trailing characters

Sometimes numbers include spaces, letters, or symbols before or after your numbers. Unwanted characters can be removed with a few handy formulas called TRIM, LEFT, RIGHT, and LEN.

  • TRIM – removes leading and trailing spaces from a cell
  • LEFT – extracts a certain number of characters from a cell starting from the left
  • RIGHT – extracts a certain number of characters from a cell starting from the right
  • LEN – gives the length of a cell (number of characters like letters, numbers, and symbols)

In the examples below, notice that only the first row shows the green triangle that will allow you to use the Convert to Number feature.

So how do you convert the rest of them?

You can use a combination of the above formulas with the 0+ from the previous method.

=0+TRIM(A1)=0+LEFT(A2, LEN(A2)-1)=0+RIGHT(A3, LEN(A3)-5)=0+LEFT(RIGHT(A4, LEN(A4)-1),LEN(A4)-1-5)
  • ROW 1 – The number looks okay at first glance, but it actually has 2 spaces before and after the number like [space][space]30[space][space]. You can use the TRIM formula to remove unwanted leading and trailing spaces from a cell.
  • ROW 2 – The LEFT formula can be combined with LEN to get the whole value of the cell except the last character. Because there is only one unwanted symbol (#), then you only need to subtract 1. If there were 2 unwanted characters (##) at the end, then you could say -2 instead of -1.
  • ROW 3 – This works the same as the LEFT formula in ROW 2 except it starts counting from the right side of the cell.
  • ROW 4 – In this example both are combined to remove both a prefix and a suffix from a formula. Notice that the RIGHT formula only subtracts 1 from the LEN, but the LEFT formula subtracts both 1 and 5. (After the RIGHT formula produces a result it is already 1 shorter than the original LEN.)

Leading Zeros

Now you know how to convert text to numbers, but in some cases, you may actually NOT want numbers to act like numbers. You may want the numbers to act like text.

A very common example of when leading zeros are necessary is with ZIP codes. Several ZIP codes in the northeastern US start with zeros. If you open a CSV file in Excel, it will usually remove leading zeros. There are 3 great ways to correct this issue so Excel will show the correct ZIP code.

Method 1: TEXT formula

Here’s an example of how to use the TEXT formula.

=TEXT(a2, "00000")
The “00000” tells the TEXT formula to format as text that is 5 digits long and adds leading zeros if they aren’t present. So the 2383 turns into 02383 and the 21201 appears unaffected.

The TEXT formula converts any numbers that you give it into text, so notice our little green triangles showing up again. You can no longer use formulas with math calculations, and in the case of ZIP codes, that’s not a problem.

There are plenty of other format codes available besides just “00000” which can be found in the Excel documentation here.

Method 2: Custom format

If you want to keep the number stored as a number or want to avoid using a formula. You can use custom formatting for cells to make the numbers show up exactly how you want.

right click > format cells

1. Highlight the cells you want to reformat and right click to select “Format Cells…”

custom format options

2. On the Number tab, select Custom.

enter "00000" as custom format

3. Then enter 00000 where it defaults to “General”.

corrected zip codes

4. Now the ZIP code will show with the proper leading zeros.

Method 3: Open CSV into Excel and preserve leading zeros

The last option can be useful when opening a large CSV file with lots of potential leading zero issues is using the Data Import feature. It’s great because you don’t have to change formatting and copy and paste a lot of values.

1. Open a blank workbook or worksheet where you want the data to go.

Data tab then "From Text"

2. On the Excel ribbon at the top, select the Data tab and then select “From Text”.

open file dialog

3. Select the CSV file that you want to import.

text import wizard > Step 1: data type

4. A wizard will walk you through the import steps. The first question is the Data Type (either Delimited or Fixed width). For CSV files select delimited, then click Next.

text import wizard > Step 2: delimiter

5. The next step asks you what characters are used as delimiters. For CSV files, you can usually select Comma and not change any other options, then click Next.

text import wizard > Step 3: column data format preview

6. The last step asks you about formatting. This is the key step for preventing any formatting issues. Select a column in the preview pane and change the options to match how you want the numbers to be treated.

text import wizard > Step 3: column data format preview with type changed to text

7. In this case, we want the quantity to be treated like a number, so we leave it as General, but we want ZIP codes to be treated like text, so we selected the first column then the Text option.

Where do you want to put the data dialog

8. When you click Finish, Excel will ask you where you want the data to go (in a new sheet or in an existing sheet). You can usually just click OK.

imported data in cells formatted correctly

9. Now your data is imported with the leading zeros on your ZIP codes!

We encourage you to try out these examples and let us know if you have any questions. We hope these methods help you as you clean your data! Please let us know if there are other topics or data cleaning problems you would like us to tackle by emailing contact@inciter.io.

Tags:
Jacob Joseph
jacob@inciter.io

Mr. Joseph is the Solutions Architect at Inciter. He has architected, developed and managed multiple data management solutions in multiple industries including higher education, K-12, manufacturing, entertainment, and retail. He received an M.P.S. in Information Visualization in 2015 and specializes in data modeling, extract-transform-load (ETL) automation, systems integrations, interactive web visualizations, and sustainable data systems.

Post a Comment