Some Excel values look like numbers, but don't add up, because Excel thinks they are text. With the techniques in the article, you can convert text "numbers" to real numbers.
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, as you can see below.
In the screen shot above, the values in column C look like numbers, but they don't add up -- the total is zero.
At the right, the COUNTA function is used in cell F4, and it shows that there are 4 entries in that range of cells.
However, the COUNT function in cell F5 show a result of zero -- none of the values in cells C3:C6 are recognized as numers.
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. If you need to do this frequently, you can use a macro to automate the task.
NOTE: The "Convert Text to Numbers" feature is available in my Contextures Excel Tools add-in
To fix numbers that are seen as text, follow these steps:
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.
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 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
If dates are formatted with slashes, such as 4/5/14, you can try to convert them to real dates by replacing the slashes.
After the dates have been fixed, you can apply formatting, by using the Number Format commands on the Ribbon's Home tab.
If you copy data from a website, it might include hidden characters, such a non-breaking space. In Excel, this is character 160, and it is not fixed by some of the number cleanup techniques. You can search for that character, and replace it.
If you need to do this frequently, you can use a macro to automate the task.
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
If a worksheet has currency in a format with different separators, use the Text To Columns command to convert the values. For example, change German currency -- 987.654,32 -- 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.
Note: If 'Trailing minus for negative numbers' is checked, you can
click Finish in Step 1 of the Text to Columns wizard.
Thanks to Bob Ryan, from Simply Learning Excel, who sent this formula to fix imported numbers with trailing minus signs.
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.
The minus sign before the VALUE function changes the value to a negative
amount.
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, 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 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.
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.
1. Data Entry -- Tips
2. Data Entry -- Fill Blank Cells
3. Data Entry -- Convert Text to Numbers
4. Data Entry -- Increase Numbers With
Paste Special
____________
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Search Contextures Sites
Last updated: October 26, 2018 9:23 AM