Sorting (Numbers with Text)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list of distances, that when I pull into a Pivot Table it sorts them
by the "spelling" of the number, I have tried changing the type of field
(Number, General, Text, etc). Nothing seems to work. Any suggestions?

0-5 miles
10-15 miles
105-110 miles
150-155 miles
15-20 miles
175-180 miles
20-25 miles
25-30 miles
30-35 miles
35-40 miles
40-45 miles
45-50 miles
50-55 miles
5-10 miles
60-65 miles
65-70 miles
85-90 miles
Non-Responders
 
Hi

Why not break the list to 2 columns?

1. Copy the same column and paste to right;
2. Select first column;
3. Replace " -*" with nothing;
4. select the second column;
5. Replace "*- " with nothing;
6. Sort by the order of the first colmn
 
Not sure how to complete what you suggest. If I have the same data in 2
columns, then select the 1st column, do a Replace, I have to enter the
following:

Find What:
Replace With:

I tried to do it like:

Find What: " -*"
Replace With:

and I get this error: Microsoft Office Excel cannot find any data to
replace. Check if your search formatting and criteria are defined correctly.
 
well there are more fields in the Pivot, but the sorting problem is limited
to the column that has these distances in it.
 
Sorry but replace " -* without space i.e. only *- with nothing typed in
replacement in the first column
 
Create a Custom List with the items in the order that you want them sorted:

Type the list in order on a worksheet, and select the cells in the list
Choose Tools>Options, and click the Customs Lists tab
Click Import, to add your items as a list
Click OK
Refresh the pivot table, and sort the Miles field.
 
Sorry again,
but replace " -*" without space i.e. only:
-*
with
nothing typed in replacement in the first column

similarly replace "*- " without space i.e. only:
-*
with
nothing typed in replacement in the secoind column
and then
sort by the order of first column

in the source sheet for a pivot

Does this help???
 
It would be very helpful if you could describe what it is you are trying to
accomplish.

Frank
 
Back
Top