Incomplete sorting

  • Thread starter Thread starter light
  • Start date Start date
L

light

I have a simple worksheet with column A on the left with 31 names.
After highlighting the data that goes with the names I click
Data>sort>Column A>(there is a header on top) finish. It correctly
sorts the first 24 names. The next 7 start with a another subsort of
those 7 names. So after the 24th name that starts with V,I get another
name that starts with D and sorts the remaining 6 names from there.
 
light

Sounds to me like you have some names with a leading space. The ones with a
leading space would sort first.

Manually remove spaces by using a helper column and entering =TRIM(A1) and
drag/copy down. This will remove leading and trailing spaces but not regular
spaces between names.

Copy and Paste Special>Values on this helper column. Try the sort again.

VBA Macro.....

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub

Gord Dibben Excel MVP
 
Back
Top