Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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" wrote in message ... 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
|
|||
|
|||
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
|
|||
|
|||
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" wrote in message ... 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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tell users how to sort 5 digit and 9 digit zipcodes correctly aft. | New Users to Excel | |||
sort a column of numbers with 100510 and 10160 so they are in th. | Excel Discussion (Misc queries) | |||
How to sort random numbers in columns | Excel Discussion (Misc queries) | |||
Randomly Sort a Series of Numbers | Excel Discussion (Misc queries) | |||
sort numbers in EXCEL? | Excel Worksheet Functions |