Sorting (Numbers with Text)

[JSpence2003]

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

 

[FARAZ QURESHI]

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

 

[JSpence2003]

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.

 

[Frank Pytel]

What is it that you are attempting to do. What will the pivot chart accomplish?

Frank

 

[JSpence2003]

well there are more fields in the Pivot, but the sorting problem is limited
to the column that has these distances in it.

 

[FARAZ QURESHI]

Sorry but replace " -* without space i.e. only *- with nothing typed in
replacement in the first column

 

[Debra Dalgleish]

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.

 

[FARAZ QURESHI]

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???

 

[Frank Pytel]

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

Frank