Some Microsoft Excel values look like numbers, but don't add up. Or, you can't change the formatting for Excel numbers - it always stays the same. Try the solutions in this article, to fix those broken numbers.
For date formatting problems, see the Excel Dates Fix Format page.
NOTE: For instructions on changing written words to numbers (e.g. from Three to 3), see Words to Numbers in Excel.
If you copy data from another program, or from a text file or a web site, Excel may treat the numbers as text. In Excel, the values could look like numbers, but they don't act like numbers, and don't show a correct total, in a SUM formula, as you can see in the spreadsheet below.
Also, you can't change the number formatting - it always stays the same, no matter how you try to format cells.
In the screen shot above, the values in column C look like numbers, but they don't add up. There are 3 cells with formulas:
For some "text" numbers, you can fix the problem with Paste Special. Watch this short video tutorial, to see the steps, and the written instructions are below. The video transcript is further down the page.
NOTE: If you need to do this frequently, you can use a macro to automate the task.
Tip: After the numbers have been fixed, you can apply formatting, by using the Number Format commands on the Ribbon's Home tab.
If you frequently convert text to numbers, you can use a macro, like the one shown below.
Store the following macro in a workbook that is always open, such as your Personal Workbook. Then, add that macro to your Quick Access Toolbar, or to one of the tabs on the Excel Ribbon. To fix "text" numbers, select the cells, and click your macro button.
Sub ConvertToNumbers() Dim rng As Range 'get constants in selected range On Error Resume Next Set rng = Selection _ .SpecialCells(xlCellTypeConstants, 23) On Error GoTo errHandler If Not rng Is Nothing Then 'copy blank cell outside used range Cells.SpecialCells(xlCellTypeLastCell) _ .Offset(0, 1).Copy 'add to selected cells rng.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlPasteSpecialOperationAdd Else MsgBox "Could not find Constants in selection" End If exitHandler: Application.CutCopyMode = False Set rng = Nothing Exit Sub errHandler: MsgBox "Could not change text to numbers" Resume exitHandler End Sub
Here is another way to try to fix problem numbers. In this example, the numbers are dates, and Excel does not recognize them as real dates.
As shown in the screenshot below, these dates are formatted with slashes, in the date format: m/d/yy
To fix "text dates" in this format, you can try to convert them to real dates by using the Excel Find and Replace feature, to replace the slashes.
Follow these steps to replace the slashes, which should fix the "text" dates:
Tip: After the dates have been fixed, you can apply a different date format to the list. Select all of the fixed date cells, and use the Number Format commands on the Ribbon's Home tab.
If you copy data from a website, that data might include hidden characters, such a non-breaking space.
In Excel, a non-breaking space has a character code of 160, and that code is not fixed by some of the number cleanup techniques. Instead, you can search for that character code, and replace it with nothing (an empty string).
Tip: If you need to fix hidden characters frequently, you can use a macro to automate the task, like the macro in the section below
Follow these steps to remove the hidden characters, by using Find and Replace:
If you frequently need to remove the hidden non-breaking space character, you can use a macro.
Store the following macro in a workbook that is always open, such as your Personal Workbook. Then, add a button to an existing toolbar, and attach the macro to that button. To fix "text" numbers, select the cells, and click the toolbar button.
Sub CleanCode160() Dim rng As Range Dim arr As Variant Dim i As Long Set rng = Selection 'removes character 160 'non-breaking space 'from selected cells If rng.Cells.Count = 1 Then ReDim arr(1 To 1, 1 To 1) arr(1, 1) = rng.Value Else arr = rng.Value End If For i = 1 To UBound(arr, 1) arr(i, 1) = Replace(arr(i, 1), Chr(160), "") Next i rng.Value = arr End Sub
This quick technique, using the Excel Text to Columns feature, can fix some numbers that Excel does not recognize as real numbers.
In some cases, that changes the text numbers to real numbers.
If that technique didn't work for your data, you can try one of the other methods on this page.
If a worksheet has currency in a format that uses different separators, use the Text To Columns command to convert the values.
For example, if the data has numbers shown in German currency -- 987.654,32 -- your Excel settings might not recognize those as real numbers, because of the separators in the numbers.
Try the following steps, to convert the data to from Germany currency, to US currency -- 987,654.32
If you import numbers that have a trailing minus sign, you can use one of the following techniques to convert them to negative numbers.
With the Text to Columns feature, imported numbers with trailing minus signs can be easily converted to negative numbers.
Thanks to Bob Ryan, from Simply Learning Excel, who sent this formula to fix imported numbers with trailing minus signs.
Follow these steps to create the formula, shown in the screenshot below:
In the formula, the RIGHT function returns the last character in cell A1.
If that character is a minus sign, the VALUE function returns the number value to the left of the trailing minus sign.
In all versions of Excel, you can use the following macro to convert numbers with trailing minus signs.
Sub TrailingMinus() ' = = = = = = = = = = = = = = = = ' Use of CDbl suggested by Peter Surcouf ' Program by Dana DeLouis, email@example.com ' modified by Tom Ogilvy ' = = = = = = = = = = = = = = = = Dim rng As Range Dim bigrng As Range On Error Resume Next Set bigrng = Cells _ .SpecialCells(xlConstants, xlTextValues).Cells If bigrng Is Nothing Then Exit Sub For Each rng In bigrng.Cells If IsNumeric(rng) Then rng = CDbl(rng) End If Next End Sub
When importing data, you might be able to prevent copied numbers from being pasted as text, if you paste the data as CSV.
Here is the full transcript for the Fix Numbers That Don't Add video shown above.
If you import or copy data into Excel, such as a bank statement, sometimes the numbers don't add up correctly. We'll see how to fix that.
Here is a very small sample of a bank account. We've got cheque numbers and the amount of each cheque.
To create a total, I can go to the Home tab, and over at the right, click AutoSum. And usually, that will select any numbers above, but in this case it didn't, so I will manually select those, and press Enter, and it shows zero. So even though I've got hundreds of dollars, it's showing zero.
I'm going to add a couple of other functions on this worksheet, and just find out what's going on in these cells.
In this cell, I'm going to get a count of everything that's in those cells, whether it's text or numbers.
In here, I'm going to use COUNTA equals COUNTA open bracket. Then I'll select the cells that have the numbers, close the bracket, and press Enter.
These four cells have something in them. But how many of those have numbers? And to do that here we use COUNTA.
And in this cell, I'm going to use COUNT, and it only counts numbers. So equals COUNT open bracket, select the four cells again, close the bracket, and press Enter.
We have four cells with something in them, but none of those cells have numbers.
If I look at one of these cells and look up in the Formula Bar,
I can see the number, but in front of the number there's an apostrophe, and that indicates that this is text rather than a number.
So, whatever we've downloaded or copied in from somewhere came in as text.
There is a quick way we can fix this, though. We're going to select a blank cell, and then use paste special to paste it over these numbers, and it will add a zero to everything, which won't have any effect on these values, but will change them from text to numbers with that simple step.
I'll select a blank cell and copy. Then select the cells that I want to fix, and go up to Paste, click the drop-down arrow, and go down to Paste Special. And in here, I want to Add, so I'll select that. Click OK.
And now, these have all changed to numbers, and we can see a total at the bottom.
I could format these so that they all line up nicely. I could format this one, as well. And now, we have a total that's correct and nicely formatted.
Click here, to back to the Fix Numbers That Don't Add video shown above
Download the zipped file with the sample data and macros. The zipped file is in xlsm format, and contains macros, so enable them to test the code.
Last updated: May 11, 2022 10:24 AM