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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?