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


"JSpence2003" wrote:

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

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.

"FARAZ QURESHI" wrote:

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" wrote:

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

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

"JSpence2003" wrote:

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.

"FARAZ QURESHI" wrote:

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" wrote:

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

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

"FARAZ QURESHI" wrote:

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

"JSpence2003" wrote:

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.

"FARAZ QURESHI" wrote:

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" wrote:

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

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

Frank

"JSpence2003" wrote:

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

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



"Frank Pytel" wrote:

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

Frank

"JSpence2003" wrote:

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

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

Frank

"JSpence2003" wrote:

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



"Frank Pytel" wrote:

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

Frank

"JSpence2003" wrote:

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

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 ToolsOptions, 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.

JSpence2003 wrote:
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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html