Numbers won't sort correctly.

[Guest]

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?

 

[Roger Govier]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Fern,

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

 

[Guest]

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("P150")

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

 

[Guest]

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

Fern

 

[Guest]

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

 

[Guest]

Thanks Peter!
It worked beautifully.

Fern

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

 

[Guest]

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