Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Worthington, Ohio, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Alphanumeric character conversion (Excel 2000)

    I have a spreadsheet that was exported from one of our booking systems. When I try to sort column B, it sorts it in a descending order, however, higher numbers appear in the middle of the list. That shouldn't have happened. My CIO seems to think that it's something to do with the way the system converts to alphanumeric characters. I need it to read as numbers. How can I convert entire columns to display as numbers? I've already tried all the "formatting cell" options.

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Alphanumeric character conversion (Excel 2000)

    There are a number of ways to do this.
    A quick way that works in this example is to select column B, then choose "Data>Text to columns..."
    Click "next" twice to accept the default settings and then click "Finish". The text values will now be converted to numbers.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Alphanumeric character conversion (Excel 2000)

    The numbers are imported as text (which is why the leading zeros are retained). Use Tony's method for any column, or place a 1 on the sheet, copy it, select all the number cells, and then select Edit, Paste Special, Multiply, OK out then run your sort. To continue to show the numbers with leading zeros use a custom format such as '000'. Also, search the forum for macros to change number text to numbers. Here's the generic one I use:

    Sub Text2Values()
    Application.ScreenUpdating = False
    Dim rngOpOn As Range, rngCell As Range
    Set rngOpOn = Selection.SpecialCells(xlCellTypeConstants, xlNumbers + xlTextValues)
    If Not rngOpOn Is Nothing Then
    For Each rngCell In rngOpOn
    rngCell.Value = rngCell.Value
    Next rngCell
    End If
    ActiveCell.Select
    Application.ScreenUpdating = True
    End Sub
    -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
  •