Sorting (Numbers with Text)
 

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

Sorting (Numbers with Text)
 

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

Sorting (Numbers with Text)
 

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

Sorting (Numbers with Text)
 

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

Sorting (Numbers with Text)
 

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

Sorting (Numbers with Text)
 

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

Sorting (Numbers with Text)
 

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

Sorting (Numbers with Text)
 

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

Sorting (Numbers with Text)
 

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