+ Reply to Thread
Results 1 to 11 of 11

Numbers won't sort correctly.

  1. #1
    FernW
    Guest

    Numbers won't sort correctly.

    I have a column of numbers that were imported from a web site and they won't
    sort correctly. I have tried reformatting the cells as general numbers but
    that doesn't change anything. I discovered that numbers below 100 have a
    space or invisible data in front of the number. If I remove it, the number
    then shifts to right alignment in the cell and will sort as a number. Is
    there any way I can remove these spaces from all numbers at once instead of
    going down the column row by row?

  2. #2
    Roger Govier
    Guest

    Re: Numbers won't sort correctly.

    Hi Fern

    One way
    Type a 1 into a blank cell on your sheet. Copy the cell. Mark the range of
    your data Edit=>Paste Special=>Multiply.

    --
    Regards
    Roger Govier
    "FernW" <[email protected]> wrote in message
    news:[email protected]...
    >I have a column of numbers that were imported from a web site and they
    >won't
    > sort correctly. I have tried reformatting the cells as general numbers but
    > that doesn't change anything. I discovered that numbers below 100 have a
    > space or invisible data in front of the number. If I remove it, the number
    > then shifts to right alignment in the cell and will sort as a number. Is
    > there any way I can remove these spaces from all numbers at once instead
    > of
    > going down the column row by row?




  3. #3
    Peter Rooney
    Guest

    RE: Numbers won't sort correctly.

    You could select the range of cells containing the values then run this
    little macro...

    Sub TrimStrings()
    Dim TrimCell As Object
    For Each TrimCell In Selection
    TrimCell.Value = Trim(Str(TrimCell.Value))
    Next TrimCell
    End Sub

    Hope this helps

    Pete



    "FernW" wrote:

    > I have a column of numbers that were imported from a web site and they won't
    > sort correctly. I have tried reformatting the cells as general numbers but
    > that doesn't change anything. I discovered that numbers below 100 have a
    > space or invisible data in front of the number. If I remove it, the number
    > then shifts to right alignment in the cell and will sort as a number. Is
    > there any way I can remove these spaces from all numbers at once instead of
    > going down the column row by row?


  4. #4
    FernW
    Guest

    Re: Numbers won't sort correctly.

    Roger,
    Thanks for your response.

    I tried this and nothing happened. Perhaps I don't understand you correctly.
    When you say "Mark the range of your data" do you mean select the data?
    That's what I did and when I went to Edit=>Paste Special=>Multiply, Excel
    just put a flashing border around the cell that contained the 1.

    Fern


    "Roger Govier" wrote:

    > Hi Fern
    >
    > One way
    > Type a 1 into a blank cell on your sheet. Copy the cell. Mark the range of
    > your data Edit=>Paste Special=>Multiply.
    >
    > --
    > Regards
    > Roger Govier
    > "FernW" <[email protected]> wrote in message
    > news:[email protected]...
    > >I have a column of numbers that were imported from a web site and they
    > >won't
    > > sort correctly. I have tried reformatting the cells as general numbers but
    > > that doesn't change anything. I discovered that numbers below 100 have a
    > > space or invisible data in front of the number. If I remove it, the number
    > > then shifts to right alignment in the cell and will sort as a number. Is
    > > there any way I can remove these spaces from all numbers at once instead
    > > of
    > > going down the column row by row?

    >
    >
    >


  5. #5
    FernW
    Guest

    RE: Numbers won't sort correctly.

    Hi Peter,
    Thanks so much for your response. This worked perfectly. Is there any way to
    add a couple of lines so I can select the whole column rather than just the
    rows that contain data? I tried to do it by adding an If... Then but it
    didn't work.

    Thanks.
    Fern


    "Peter Rooney" wrote:

    > You could select the range of cells containing the values then run this
    > little macro...
    >
    > Sub TrimStrings()
    > Dim TrimCell As Object
    > For Each TrimCell In Selection
    > TrimCell.Value = Trim(Str(TrimCell.Value))
    > Next TrimCell
    > End Sub
    >
    > Hope this helps
    >
    > Pete
    >
    >
    >
    > "FernW" wrote:
    >
    > > I have a column of numbers that were imported from a web site and they won't
    > > sort correctly. I have tried reformatting the cells as general numbers but
    > > that doesn't change anything. I discovered that numbers below 100 have a
    > > space or invisible data in front of the number. If I remove it, the number
    > > then shifts to right alignment in the cell and will sort as a number. Is
    > > there any way I can remove these spaces from all numbers at once instead of
    > > going down the column row by row?


  6. #6
    Peter Rooney
    Guest

    RE: Numbers won't sort correctly.

    Fern,



    "FernW" wrote:

    > Hi Peter,
    > Thanks so much for your response. This worked perfectly. Is there any way to
    > add a couple of lines so I can select the whole column rather than just the
    > rows that contain data? I tried to do it by adding an If... Then but it
    > didn't work.
    >
    > Thanks.
    > Fern
    >
    >
    > "Peter Rooney" wrote:
    >
    > > You could select the range of cells containing the values then run this
    > > little macro...
    > >
    > > Sub TrimStrings()
    > > Dim TrimCell As Object
    > > For Each TrimCell In Selection
    > > TrimCell.Value = Trim(Str(TrimCell.Value))
    > > Next TrimCell
    > > End Sub
    > >
    > > Hope this helps
    > >
    > > Pete
    > >
    > >
    > >
    > > "FernW" wrote:
    > >
    > > > I have a column of numbers that were imported from a web site and they won't
    > > > sort correctly. I have tried reformatting the cells as general numbers but
    > > > that doesn't change anything. I discovered that numbers below 100 have a
    > > > space or invisible data in front of the number. If I remove it, the number
    > > > then shifts to right alignment in the cell and will sort as a number. Is
    > > > there any way I can remove these spaces from all numbers at once instead of
    > > > going down the column row by row?


  7. #7
    Peter Rooney
    Guest

    RE: Numbers won't sort correctly.

    Fern,

    I modified the code a bit while you were away - now it aligns any numbers to
    the right and any labels to the left, too. I defined a range variable
    SearchRange and set it to refer to a range of cells at the beginning of the
    procedure - you could equally set it to "A1:A10000" if you want, but
    remember, the bigger the range, the longer the macro will take to run - no
    point scanning it all if you never fill more than 500 rows!

    Sub TrimStrings()
    Dim TrimCell As Object
    Dim SearchRange As Range

    'Set SearchRange = ActiveSheet.Range("P1:P50")

    For Each TrimCell In SearchRange
    If IsNumeric(Trim(TrimCell.Formula)) Then
    With TrimCell
    .Formula = Trim(Str(TrimCell.Formula))
    .HorizontalAlignment = xlRight
    End With
    Else
    With TrimCell
    .Formula = Trim(TrimCell.Formula)
    .HorizontalAlignment = xlLeft
    End With
    End If
    Next TrimCell
    End Sub

    Hope this helps

    Pete


    "FernW" wrote:

    > Hi Peter,
    > Thanks so much for your response. This worked perfectly. Is there any way to
    > add a couple of lines so I can select the whole column rather than just the
    > rows that contain data? I tried to do it by adding an If... Then but it
    > didn't work.
    >
    > Thanks.
    > Fern
    >
    >
    > "Peter Rooney" wrote:
    >
    > > You could select the range of cells containing the values then run this
    > > little macro...
    > >
    > > Sub TrimStrings()
    > > Dim TrimCell As Object
    > > For Each TrimCell In Selection
    > > TrimCell.Value = Trim(Str(TrimCell.Value))
    > > Next TrimCell
    > > End Sub
    > >
    > > Hope this helps
    > >
    > > Pete
    > >
    > >
    > >
    > > "FernW" wrote:
    > >
    > > > I have a column of numbers that were imported from a web site and they won't
    > > > sort correctly. I have tried reformatting the cells as general numbers but
    > > > that doesn't change anything. I discovered that numbers below 100 have a
    > > > space or invisible data in front of the number. If I remove it, the number
    > > > then shifts to right alignment in the cell and will sort as a number. Is
    > > > there any way I can remove these spaces from all numbers at once instead of
    > > > going down the column row by row?


  8. #8
    FernW
    Guest

    RE: Numbers won't sort correctly.

    Pete,
    I tried it twice and got the message: Object required

    Fern


    "Peter Rooney" wrote:

    > Fern,
    >
    > I modified the code a bit while you were away - now it aligns any numbers to
    > the right and any labels to the left, too. I defined a range variable
    > SearchRange and set it to refer to a range of cells at the beginning of the
    > procedure - you could equally set it to "A1:A10000" if you want, but
    > remember, the bigger the range, the longer the macro will take to run - no
    > point scanning it all if you never fill more than 500 rows!
    >
    > Sub TrimStrings()
    > Dim TrimCell As Object
    > Dim SearchRange As Range
    >
    > 'Set SearchRange = ActiveSheet.Range("P1:P50")
    >
    > For Each TrimCell In SearchRange
    > If IsNumeric(Trim(TrimCell.Formula)) Then
    > With TrimCell
    > .Formula = Trim(Str(TrimCell.Formula))
    > .HorizontalAlignment = xlRight
    > End With
    > Else
    > With TrimCell
    > .Formula = Trim(TrimCell.Formula)
    > .HorizontalAlignment = xlLeft
    > End With
    > End If
    > Next TrimCell
    > End Sub
    >
    > Hope this helps
    >
    > Pete
    >
    >
    > "FernW" wrote:
    >
    > > Hi Peter,
    > > Thanks so much for your response. This worked perfectly. Is there any way to
    > > add a couple of lines so I can select the whole column rather than just the
    > > rows that contain data? I tried to do it by adding an If... Then but it
    > > didn't work.
    > >
    > > Thanks.
    > > Fern
    > >
    > >
    > > "Peter Rooney" wrote:
    > >
    > > > You could select the range of cells containing the values then run this
    > > > little macro...
    > > >
    > > > Sub TrimStrings()
    > > > Dim TrimCell As Object
    > > > For Each TrimCell In Selection
    > > > TrimCell.Value = Trim(Str(TrimCell.Value))
    > > > Next TrimCell
    > > > End Sub
    > > >
    > > > Hope this helps
    > > >
    > > > Pete
    > > >
    > > >
    > > >
    > > > "FernW" wrote:
    > > >
    > > > > I have a column of numbers that were imported from a web site and they won't
    > > > > sort correctly. I have tried reformatting the cells as general numbers but
    > > > > that doesn't change anything. I discovered that numbers below 100 have a
    > > > > space or invisible data in front of the number. If I remove it, the number
    > > > > then shifts to right alignment in the cell and will sort as a number. Is
    > > > > there any way I can remove these spaces from all numbers at once instead of
    > > > > going down the column row by row?


  9. #9
    Peter Rooney
    Guest

    RE: Numbers won't sort correctly.

    Fern,

    There's a comma in front of the Set SearchRange command here - remove it, as
    it serves to comment out code - if the line is commented out, the code has no
    range to work with - hence the message - sorry!

    Pete



    "FernW" wrote:

    > Pete,
    > I tried it twice and got the message: Object required
    >
    > Fern
    >
    >
    > "Peter Rooney" wrote:
    >
    > > Fern,
    > >
    > > I modified the code a bit while you were away - now it aligns any numbers to
    > > the right and any labels to the left, too. I defined a range variable
    > > SearchRange and set it to refer to a range of cells at the beginning of the
    > > procedure - you could equally set it to "A1:A10000" if you want, but
    > > remember, the bigger the range, the longer the macro will take to run - no
    > > point scanning it all if you never fill more than 500 rows!
    > >
    > > Sub TrimStrings()
    > > Dim TrimCell As Object
    > > Dim SearchRange As Range
    > >
    > > 'Set SearchRange = ActiveSheet.Range("P1:P50")
    > >
    > > For Each TrimCell In SearchRange
    > > If IsNumeric(Trim(TrimCell.Formula)) Then
    > > With TrimCell
    > > .Formula = Trim(Str(TrimCell.Formula))
    > > .HorizontalAlignment = xlRight
    > > End With
    > > Else
    > > With TrimCell
    > > .Formula = Trim(TrimCell.Formula)
    > > .HorizontalAlignment = xlLeft
    > > End With
    > > End If
    > > Next TrimCell
    > > End Sub
    > >
    > > Hope this helps
    > >
    > > Pete
    > >
    > >
    > > "FernW" wrote:
    > >
    > > > Hi Peter,
    > > > Thanks so much for your response. This worked perfectly. Is there any way to
    > > > add a couple of lines so I can select the whole column rather than just the
    > > > rows that contain data? I tried to do it by adding an If... Then but it
    > > > didn't work.
    > > >
    > > > Thanks.
    > > > Fern
    > > >
    > > >
    > > > "Peter Rooney" wrote:
    > > >
    > > > > You could select the range of cells containing the values then run this
    > > > > little macro...
    > > > >
    > > > > Sub TrimStrings()
    > > > > Dim TrimCell As Object
    > > > > For Each TrimCell In Selection
    > > > > TrimCell.Value = Trim(Str(TrimCell.Value))
    > > > > Next TrimCell
    > > > > End Sub
    > > > >
    > > > > Hope this helps
    > > > >
    > > > > Pete
    > > > >
    > > > >
    > > > >
    > > > > "FernW" wrote:
    > > > >
    > > > > > I have a column of numbers that were imported from a web site and they won't
    > > > > > sort correctly. I have tried reformatting the cells as general numbers but
    > > > > > that doesn't change anything. I discovered that numbers below 100 have a
    > > > > > space or invisible data in front of the number. If I remove it, the number
    > > > > > then shifts to right alignment in the cell and will sort as a number. Is
    > > > > > there any way I can remove these spaces from all numbers at once instead of
    > > > > > going down the column row by row?


  10. #10
    FernW
    Guest

    RE: Numbers won't sort correctly.

    Thanks Peter!
    It worked beautifully.

    Fern


    "Peter Rooney" wrote:

    > Fern,
    >
    > There's a comma in front of the Set SearchRange command here - remove it, as
    > it serves to comment out code - if the line is commented out, the code has no
    > range to work with - hence the message - sorry!
    >
    > Pete
    >
    >
    >
    > "FernW" wrote:
    >
    > > Pete,
    > > I tried it twice and got the message: Object required
    > >
    > > Fern
    > >
    > >
    > > "Peter Rooney" wrote:
    > >
    > > > Fern,
    > > >
    > > > I modified the code a bit while you were away - now it aligns any numbers to
    > > > the right and any labels to the left, too. I defined a range variable
    > > > SearchRange and set it to refer to a range of cells at the beginning of the
    > > > procedure - you could equally set it to "A1:A10000" if you want, but
    > > > remember, the bigger the range, the longer the macro will take to run - no
    > > > point scanning it all if you never fill more than 500 rows!
    > > >
    > > > Sub TrimStrings()
    > > > Dim TrimCell As Object
    > > > Dim SearchRange As Range
    > > >
    > > > 'Set SearchRange = ActiveSheet.Range("P1:P50")
    > > >
    > > > For Each TrimCell In SearchRange
    > > > If IsNumeric(Trim(TrimCell.Formula)) Then
    > > > With TrimCell
    > > > .Formula = Trim(Str(TrimCell.Formula))
    > > > .HorizontalAlignment = xlRight
    > > > End With
    > > > Else
    > > > With TrimCell
    > > > .Formula = Trim(TrimCell.Formula)
    > > > .HorizontalAlignment = xlLeft
    > > > End With
    > > > End If
    > > > Next TrimCell
    > > > End Sub
    > > >
    > > > Hope this helps
    > > >
    > > > Pete
    > > >
    > > >
    > > > "FernW" wrote:
    > > >
    > > > > Hi Peter,
    > > > > Thanks so much for your response. This worked perfectly. Is there any way to
    > > > > add a couple of lines so I can select the whole column rather than just the
    > > > > rows that contain data? I tried to do it by adding an If... Then but it
    > > > > didn't work.
    > > > >
    > > > > Thanks.
    > > > > Fern
    > > > >
    > > > >
    > > > > "Peter Rooney" wrote:
    > > > >
    > > > > > You could select the range of cells containing the values then run this
    > > > > > little macro...
    > > > > >
    > > > > > Sub TrimStrings()
    > > > > > Dim TrimCell As Object
    > > > > > For Each TrimCell In Selection
    > > > > > TrimCell.Value = Trim(Str(TrimCell.Value))
    > > > > > Next TrimCell
    > > > > > End Sub
    > > > > >
    > > > > > Hope this helps
    > > > > >
    > > > > > Pete
    > > > > >
    > > > > >
    > > > > >
    > > > > > "FernW" wrote:
    > > > > >
    > > > > > > I have a column of numbers that were imported from a web site and they won't
    > > > > > > sort correctly. I have tried reformatting the cells as general numbers but
    > > > > > > that doesn't change anything. I discovered that numbers below 100 have a
    > > > > > > space or invisible data in front of the number. If I remove it, the number
    > > > > > > then shifts to right alignment in the cell and will sort as a number. Is
    > > > > > > there any way I can remove these spaces from all numbers at once instead of
    > > > > > > going down the column row by row?


  11. #11

    Re: Numbers won't sort correctly.

    Hi Fern, I also have this problem and has noticed that Excel Help suggests
    the same solution as Roger did. I have tried this too.. nothing happens ..
    Now I will try out that macro thing.. hope it works.. (It is sooo freakin
    stupid that Microsoft has a lot of solutions in Help that doesn't really
    work!!)... Bregards Jonna

    "FernW" wrote:

    > Roger,
    > Thanks for your response.
    >
    > I tried this and nothing happened. Perhaps I don't understand you correctly.
    > When you say "Mark the range of your data" do you mean select the data?
    > That's what I did and when I went to Edit=>Paste Special=>Multiply, Excel
    > just put a flashing border around the cell that contained the 1.
    >
    > Fern
    >
    >
    > "Roger Govier" wrote:
    >
    > > Hi Fern
    > >
    > > One way
    > > Type a 1 into a blank cell on your sheet. Copy the cell. Mark the range of
    > > your data Edit=>Paste Special=>Multiply.
    > >
    > > --
    > > Regards
    > > Roger Govier
    > > "FernW" <[email protected]> wrote in message
    > > news:[email protected]...
    > > >I have a column of numbers that were imported from a web site and they
    > > >won't
    > > > sort correctly. I have tried reformatting the cells as general numbers but
    > > > that doesn't change anything. I discovered that numbers below 100 have a
    > > > space or invisible data in front of the number. If I remove it, the number
    > > > then shifts to right alignment in the cell and will sort as a number. Is
    > > > there any way I can remove these spaces from all numbers at once instead
    > > > of
    > > > going down the column row by row?

    > >
    > >
    > >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1