Want the post in Swedish? Click here

Today we will continue with washing data and addressing the problem when the amount is in text format. The reason why we encounter numbers in text format is often due to the fact that we have exported data from, for example, an accounting system or received external data. The numbers can then be formatted as text or there might be spaces between the numbers. Today we will look at the former.

Determine if the amount is in text format

There are several ways to determine if the numbers are in text format.

The first way I want to show is that under the start menu you can see how the numbers are formatted.

Furthermore… you can see a Green Marking at the numbers as below. If you click on the cell, you will see an Exclamation mark which you can right-click on.

You may also get a value error when you have a formula that refers to a cell with text format.

The function I use most for text control is below where I mark the amounts and if I only see “Count” (picture 2) in the status bar and not a sum, then I know that the amounts are incorrectly formatted.

• 

• 

By customising the status bar to display the numerical count, you can quickly see if the cells contains the same count.

Customise the status bar

Customising the status bar is easy and is done by right-clicking on the bar and clicking for the factors you want to display. I usually have; Average, Number, Numeric Number and Sum among my choices.

The function ISTEXT

If you are unsure whether you have text format in certain cells, you can easily test this by using the formula “ISTEXT”. If you receive the message TRUE, the amounts are text.

Solution 1: Convert to number

If you have done the above test and added a filter, you can convert to number by selecting the cells, press “!” and select “Convert to number”:

• 

• 

• 

Solution 2: Change text to number with Paste Special

This slightly more complicated way is seldom used, but you sometimes have to test different methods until finding what works.

First format the cells to “General”.

Select a blank cell and copy it using ……Ctrl + C

Go back to the text area and right-click bring up the Paste Special command.

Select “Values” and “Add”…OK.

Problem solved!!

Solution 3: Multiply

The last suggestion is simply multiplying the amounts with text format by the number 1 to convert the amounts to numbers. This method works well if you have an extensive column with text formatting on different rows.

You can in the same way copy the number 1 and then mark the area with text format, right-click and choose paste special and “multiply” everything. This is similar to option 2 above.

Solution 4: Convert text to columns

The last option is to select the cells with text format, click on the “Text to columns” converter and click on finish without doing anything more. I was not familiar with this approach before I started this post but picked it up on Google when I wanted to check if there was any more effective solutions.

Next post

In today’s post, we have looked at several ways to identify if amounts are in fact in text format and no less than 4 different ways to reformat these where Convert to number is my favourite. What you have to keep in mind is that you sometimes need to test different methods to see which one works, whereby it is good to know some alternatives. In the next post I will show when and how to use Find and Replace to wash data.

Feel free to connect with me on LinkedIn and visit Learnesy´s website for more information.

Carl Stiller in collaboration with Learnesy