Search Contextures Sites

Excel Data Entry -- Convert Text to Numbers

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


If you copy data from another program, such as Microsoft Access, or from a text file, Excel may treat the numbers as text.* Calculations that use these numbers will be incorrect, as in the SUM shown above.

There are several methods that you can use to convert the text "numbers" to real numbers.

 

  

Convert Text to Numbers with Paste Special

  1. Select a blank cell
  2. Choose Edit>Copy
  3. Select the cells that contain the numbers
  4. Choose Edit>Paste Special
  5. Select Add
  6. Click OK
  7. To apply number formatting, choose Format>Cells
  8. On the Number tab, select the appropriate format, then click OK.

Watch the Video

View the steps described above, in a short Paste Special video.

 

 

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.

  1. Select the cells that contain the dates
  2. Choose Edit>Replace
  3. For Find what, type a forward slash:   /
  4. For Replace with, type a forward slash:   /
  5. Click Replace All
  6. To apply date formatting, choose Format>Cells
  7. On the Number tab, select a date format, then click OK.

 

 

Convert Text to Numbers with Text to Columns

  1. Select the cells that contain the numbers
  2. Choose Data>Text to Columns
  3. 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.

  1. Select the cells that contain the numbers
  2. Choose Data>Text to Columns
  3. To view the Trailing Minus setting, click Next, click Next
  4. In Step 3, click the Advanced button
  5. Check the box for 'Trailing minus for negative numbers', click OK
  6. 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.

  1. In this example, the first number with a trailing 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 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.

  1. Copy the data in the other program
  2. Switch to Excel
  3. Select the cell where the paste will start
  4. Choose Edit>Paste Special
  5. 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

   

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 ©2012
All rights reserved.

 

Last updated: October 29, 2011 3:24 PM