Search Contextures Sites

Contextures Excel news by email Email: Name (optional): We respect your email privacy	Fix Excel Numbers That Don't Add Up 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. Then, 1 + 1 will equal 2, instead of zero. For instructions on changing written words to numbers (e.g. from Three to 3), see Words to Numbers in Excel. Look Like Numbers, But Don't Add Up Convert Text to Numbers with Paste Special Convert Dates with Replace All Convert Text to Numbers with Text to Columns Convert Trailing Minus Signs Convert Trailing Minus Signs - Formula Convert Trailing Minus Signs Programmatically Paste as CSV Convert Text to Numbers with VBA Look Like Numbers, But Don't Add Up If you copy data from another program, such as Microsoft Access, or from a text file, Excel may treat the numbers as text.* In Excel, the values 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 B looks like numbers, but they don't add up -- the total is zero. Convert Text to Numbers with Paste Special Select a blank cell Choose Edit>Copy Select the cells that contain the numbers Choose Edit>Paste Special Select Add Click OK To apply number formatting, choose Format>Cells On the Number tab, select the appropriate format, then click OK. Watch the Video To see the steps described above, you can watch this short video tutorial. Convert Dates with Replace All If dates are formatted with slashes, such as 5/5/04, you can convert them to real dates by replacing the slashes. Select the cells that contain the dates Choose Edit>Replace For Find what, type a forward slash:   / For Replace with, type a forward slash:   / Click Replace All To apply date formatting, choose Format>Cells On the Number tab, select a date format, then click OK.   Convert Text to Numbers with Text to Columns Select the cells that contain the numbers Choose Data>Text to Columns Click Finish Convert Trailing Minus Signs In Excel 2002, and later versions, imported numbers with trailing minus signs can be easily converted to negative numbers. Select the cells that contain the numbers Choose Data>Text to Columns To view the Trailing Minus setting, click Next, click Next In Step 3, click the Advanced button Check the box for 'Trailing minus for negative numbers', click OK Click Finish Note: If 'Trailing 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 fix imported numbers with trailing minus signs. In this example, the first number with a trailing minus sign is in cell A1 Select cell B1, and enter this formula: =IF(RIGHT(A1,1)="-",-VALUE(LEFT(A1,LEN(A1)-1)),VALUE(A1)) Copy the formula down to the last row of data. 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.     Convert Trailing Minus Signs Programmatically 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 Paste as CSV To prevent copied numbers from being pasted as text, you may be able to paste the data as CSV. Copy the data in the other program Switch to Excel Select the cell where the paste will start Choose Edit>Paste Special Select CSV, click OK   Convert Text to Numbers With VBA If you frequently convert text to numbers, you can use a macro. Add a button to an existing toolbar, and attach the macro to that button. Then, select the cells, and click the toolbar button. Sub ConvertToNumbers() Cells.SpecialCells(xlCellTypeLastCell) _ .Offset(1, 1).Copy Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlPasteSpecialOperationAdd With Selection .VerticalAlignment = xlTop .WrapText = False End With Selection.EntireColumn.AutoFit End Sub *For Excel 2002, the problem with Access data has been fixed in Office XP Service Pack 3. There is information in the following MSKB article: Numbers that are copied from Access 2002 paste as text in Excel 2002   http://support.microsoft.com/default.aspx?id=328933 More Data Entry Tutorials 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 Excel Data Entry Videos Number keypad with Excel shortcuts: ____________

 

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2013 All rights reserved.	Debra Dalgleish

Last updated: May 11, 2013 11:42 AM