Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Petersburg, Virginia
    Thanked 0 Times in 0 Posts

    Converting cell to number (XP sp2)

    I frequently copy data from Access to Excel or from one Excel spreadsheet to another. The copied NUMBERS appear with the little green triangle and allow me to change the cell contents to a NUMBER. (I never wanted anything other than a number, but that's beside the point.) How can I get these cells to copy as the numbers they started out as without having to "convert to number" on cell at a time?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Salt Lake City, Utah, USA
    Thanked 5 Times in 5 Posts

    Re: Converting cell to number (XP sp2)

    There is a known problem with numbers copied from Access being pasted or imported to Excel as as Text. A manual way to fix them, one column at a time, is use Data, Text-to-Columns, Fixed Width, Finish, to copy the data over itself. Another fix is to enter a 1 in an unused cell, and copy it, select the cells in question, and Paste Special, Multiply. Another is to use code like this:

    Sub Txt2Val()
    Dim rngAct As Range, rngCell As Range
    On Error Resume Next
    Set rngAct = Selection.SpecialCells(xlCellTypeConstants, xlErrors + _
    xlLogical + xlNumbers + xlTextValues)
    If Not rngAct Is Nothing Then
    For Each rngCell In rngAct
    rngCell.Value = rngCell.Value
    Next rngCell
    End If
    Set rngAct = Nothing
    End Sub

    I think that saving the file as HTML, then opening the HTML file and resaving it as *.xls also fixes this.

    However, you should not this problem with numeric formatted data copied form Excel to Excel. Are you sure that when this happens the source cells are numeric? Could you post a sample?
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts