Fix excel numbers that are not correct (2023)

Some Microsoft Excel values ​​look like numbers, but they 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 issues, see theExcel Dates Fix Formatpage.

NOTE: For instructions onwritten words turn into numbers(e.g. from three to three), seeWords to numbers in Excel.

Look like numbers, but don't add up

If you copy data from another program or from a text file or website, Excel can treat the numbers as text. In Excel, the values ​​may look like numbers, but they don't behave like numbers or display a correct sum in a SUM formula, as you can see in the spreadsheet below.

Also, you can't change the number format - it always stays the same no matter how you try to format cells.

In the screenshot above, the values ​​in column C look like numbers, but they don't add up. There are 3 cells with formulas:

• AS: In cell C7, the total for the addition formula is zero, instead of showing the correct answer.
• NUMBER: On the right, in cell F4, the COUNTA function is used in a formula. This function counts all cells that arenot empty. The result of the formula is 4, indicating that there are 4 entries in that cell range, C3:C6.
• GRAAF: However, the COUNT function in cell F5 returns a result of zero. That shows that none of the values ​​in cells C3:C6 are recognized as numbers.

How to fix text numbers

Follow these steps to correct numbers seen as text:

1. Right-click an empty cell and click Copy
2. Select the cells containing the "text" numbers
3. Right-click one of the selected cells and click Paste Special
4. The Paste Special dialog box opens
5. Select Values ​​in the Paste section
6. Select Add in the Edit section
7. Click OK

Tip: After the numbers are captured, you can apply formatting using the Number Format commands on the Home tab of the ribbon.

Convert text to numbers with VBA

If you frequently convert text to numbers, you can use a macro as shown below.

Save the following macro in a workbook that is always open, such as your personal workbook. Than,add that macro to your Quick Access Toolbaror to one of the tabs on the Excel ribbon. To correct "text" numbers, select the cells and click your macro button.

Sub ConvertToNumbers()Dim rng As Range'get constants in selected rangeOn Error Resume NextSet rng = Selection _ .SpecialCells(xlCellTypeConstants, 23)On Error GoTo errHandlerIf Not rng Is Nothing Then 'empty cell out of used range Copy Cells.SpecialCells(xlCellTypeLastCell) _ .Offset(0, 1).Copy 'Add to selected cells rng.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlPasteSpecialOperationAddElse MsgBox "Cannot find constants in selection" End IfexitHandler: Application.CutCopyMode = False Set rng = Nothing Exit SuberrHandler: MsgBox "Could not change text to numbers" Resume exitHandlerEnd Sub

Convert dates with Replace All

Here's another way to solve 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/jj

To correct "text dates" in this format, try converting them to actual dates by using Excel's Find and Replace function to replace the slashes.

Follow these steps to replace the slashes, which should fix the "text" dates:

1. Select the cells that contain the dates
2. On the Home tab of the ribbon, click Find & Select, and then click Replace

• Or use the hotkey,Ctrl+H

• Click the Replace tab, if it is not already selected

Tip: After the dates are set, you can apply a different date format to the list. Select all cells with a fixed date and use the Number Format commands on the Home tab of the ribbon.

Recover hidden characters

If you copy data from a website, that data may contain hidden characters, such as a non-breaking space.

In Excel, a nonbreaking space has a charactercode of 160,and that code is not fixed by some number cleaning techniques. Instead, you can search for that character code and replace it with nothing (an empty string).

Tip: If you often need to correct hidden characters, you canuse a macro to automate the task, such as the macro in the section below

Follow these steps to remove the hidden characters using Find and Replace:

1. Select the cells that contain the numbers with hidden characters
2. On the Home tab of the ribbon, click Find & Select (far right)
3. In the drop-down list, click Replace
4. Click in the Search for box
5. Hold down the Alt key and type on the numeric keypad0160

• Nothing appears in the Search for box after you type that code

• Hidden characters with a code of 160 are replaced with nothing (an empty string)

Recover hidden characters with VBA

If you often need to remove the hidden nonbreaking space, you can use a macro.

Save the following macro in a workbook that is always open, such as your personal workbook. Then add a button to an existing toolbar and link the macro to that button. To correct "text" numbers, select the cells and click the toolbar button.

Sub CleanCode160()Dim rng As RangeDim arr As VariantDim i As LongSet rng = Selection'verwijdert karakter 160'vaste spatie'uit geselecteerde cellenIf rng.Cells.Count = 1 Then ReDim arr(1 To 1, 1 To 1) arr (1, 1) = rng.ValueElse arr = rng.ValueEnd IfFor i = 1 To UBound(arr, 1) arr(i, 1) = Replace(arr(i, 1), Chr(160), "")Next irng.Value = arrEnd Sub

Convert text to numbers with text to columns

This quick technique, which uses Excel's Text to Columns function, can correct some numbers that Excel doesn't recognize as real numbers.

1. Select the cells containing the numbers
2. On the Data tab of the ribbon, click Text to Columns
3. The Convert Text to Columns Wizard dialog box opens showing step 1
4. In step 1, select Delimited as File type
5. Then click the Finish button

In some cases, that changes the text numbers to real numbers.

If that technique doesn't work for your data, try one of the other methods on this page.

Convert currency with different separators

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 contains numbers that are in German currency --987.654,32-- your Excel settings may not recognize these as real numbers because of the separators in the numbers.

• German currency uses aperiodas a thousands separator, and acomeas the decimal separator
• US currency uses acomeas a thousands separator, and aperiodas the decimal separator

Try the following steps to convert the data from German currency to US currency --987.654,32

1. Select the cells containing the numbers
2. On the Data tab of the ribbon, click Text to Columns
3. The Convert Text to Columns Wizard dialog box opens showing step 1
4. Click the Next button twice (step 1 and step 2)
5. In step 3, click the Advanced button
6. From the Decimal Separator drop-down list, select the separator currently used in the values ​​-- "," (comma) in this example
7. From the thousands drop-down list, select the separator currently used in the values ​​-- "." (dot) in this example
8. Click OK and then click Finish.

Convert trailing minus signs

If you import numbers that have a minus sign, you can use one of the following techniques to convert them to negative numbers.

Use text to columns

Use a formula

Use a macro

Text to Columns

With the Text to Columns function, imported numbers with minus signs at the end can be easily converted to negative numbers.

1. Select the cells containing the numbers
2. Choose Data>Text to Columns
3. To view the Trailing Minus setting, click Next, then Next
4. In step 3, click the Advanced button
5. Check the box for 'Descending minus for negative numbers' and click OK
6. Click Finish

Note: If 'Descending minus for negative numbers' is checked, you can click Finish in step 1 of the Text to Columns wizard.

Convert trailing minus signs - formula

Thanks to Bob Ryan, from Simply Learning Excel, who sent this formula to correct imported numbers with minus signs at the end.

Follow these steps to create the formula, shown in the screenshot below:

1. In this example, the first number with a minus sign is in cell A1
2. Select cell B1 and enter this formula:
3. =IF(RIGHT(A1,1)="-",-VALUE(LEFT(A1,LEN(A1)-1)),VALUE(A1))
4. Copy the formula to the last row of data.

In the formula, the RIGHT function returns the last character in cell A1.

If that sign is a minus sign, the VALUE function returns the number value to the left of the trailing minus sign.

The minus sign before the VALUE function changes the value to a negative amount.

Programmatically convert trailing minus signs

In all versions of Excel, you can use the following macro to convert numbers with minus signs.

Sub TrailingMinus()' = = = = = = = = = = = = = = = = 'Use of CDbl suggested by Peter Surcouf' Program by Dana DeLouis, dana2@msn.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, exit Sub for each rng in bigrng .Cells If IsNumeric(rng) Then rng = CDbl(rng) End If NextEnd Sub

Paste as CSV

When importing data, you may be able to prevent copied numbers from being pasted as text if you paste the data as a CSV file.

1. Copy the data into the other program
2. Switch to Excel
3. Right-click the cell where the pasting begins and click Paste Special
4. Choose Edit > Paste Special
5. Select CSV, if it's in the list, and click OK

Transcription: Fix numbers that don't add up

Here's the full transcript for theFix songs not adding videoshown above.

---------------------------

When you import or copy data to Excel, such as a bank statement, the numbers sometimes don't add up. We'll see how we can fix that.

Here is a very small example of a bank account. We have check numbers and the amount of each check.

To do a total, I can go to the Home tab and click AutoSum on the right. And usually that selects all the numbers above, but not in this case, so I'll manually select those and press Enter, and it shows zero. So even though I have hundreds of dollars, it shows zero.

Check cell contents

I'm going to add some other functions to this worksheet and figure 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, be it text or numbers.

Here I am going to use COUNTA equals COUNTA open parenthesis. Then I select the cells with the numbers, close the parenthesis and press Enter.

These four cells have something in them. But how many of them have numbers? And to do that we use COUNTA here.

And in this cell, I'm going to use COUNT, and it only counts numbers. So equals COUNT open parenthesis, select the four cells again, close the parenthesis and press Enter.

We have four cells with something in them, but none of those cells have a number.

Check the formula bar

When I look at one of these cells and look up in the formula bar,

I can see the number, but there is an apostrophe in front of the number, indicating that this is text rather than a number.

So anything we downloaded or copied from somewhere came in as text.

Confirm the numbers

However, there is a quick way to fix this. We are going to select an empty cell and then use paste special to paste it over these numbers, and it will add a zero to everything, which will have no effect on these values, but will change them from text to numbers with that simple step.

I select an empty cell and copy. Then select the cells I want to fix and go up to Paste, click the drop down arrow and go down to Paste Special. And here I want to add, so I'll select that. Click OK.

And now these have all turned into numbers, and we can see a total at the bottom.

I could format these so they all line up nicely. I could also format it. And now we have a total that is correct and nicely formatted.

'-----------

Click here to go back to theFix songs not adding videoshown above

Download the sample file

Download thezip file containing the sample data and macros. The compressed file is in xlsm format and contains macros, so please enable them to test the code.

More data entry tutorials

Data entry tips

Excel Dates Fix Format

Fill in empty cells

Increase numbers with Paste Special

Add number to multiple cells

Excel data entry videos