Sorting characters, ignoring numbers
 

I have a list with a column of street addresses. I would like to sort by
street but ignore the house number.

for example is want the results to be:

123 Pine
456 Pine
145 Ross
668 Ross

NOT:

123 Pine
145 Ross
456 Pine
668 Ross

Any ideas?

Sorting characters, ignoring numbers
 

Barto9729 wrote:
I have a list with a column of street addresses. I would like to sort by
street but ignore the house number.

for example is want the results to be:

123 Pine
456 Pine
145 Ross
668 Ross

NOT:

123 Pine
145 Ross
456 Pine
668 Ross

Any ideas?


Assuming all of your addresses are in this exact format (might be a poor
assumption), and that you would like a secondary sort by house number (rather
than completely ignoring it), add this in another column and sort by the result:

=TRIM(MID(A1,FIND(" ",A1)+1,LEN(A1))&" "&LEFT(A1,FIND(" ",A1)-1))

Sorting characters, ignoring numbers
 

Great this works! Is there a way to put the numbers back in front now that i
have sorted them (without reordering or course)?

"Glenn" wrote:

Barto9729 wrote:
I have a list with a column of street addresses. I would like to sort by
street but ignore the house number.

for example is want the results to be:

123 Pine
456 Pine
145 Ross
668 Ross

NOT:

123 Pine
145 Ross
456 Pine
668 Ross

Any ideas?


Assuming all of your addresses are in this exact format (might be a poor
assumption), and that you would like a secondary sort by house number (rather
than completely ignoring it), add this in another column and sort by the result:

=TRIM(MID(A1,FIND(" ",A1)+1,LEN(A1))&" "&LEFT(A1,FIND(" ",A1)-1))

Sorting characters, ignoring numbers
 

Doesn't the original column still have the numbers in front? Show that column,
but sort by the other column and hide it when not needed.

Barto9729 wrote:
Great this works! Is there a way to put the numbers back in front now that i
have sorted them (without reordering or course)?

"Glenn" wrote:

Barto9729 wrote:
I have a list with a column of street addresses. I would like to sort by
street but ignore the house number.

for example is want the results to be:

123 Pine
456 Pine
145 Ross
668 Ross

NOT:

123 Pine
145 Ross
456 Pine
668 Ross

Any ideas?

Assuming all of your addresses are in this exact format (might be a poor
assumption), and that you would like a secondary sort by house number (rather
than completely ignoring it), add this in another column and sort by the result:

=TRIM(MID(A1,FIND(" ",A1)+1,LEN(A1))&" "&LEFT(A1,FIND(" ",A1)-1))

Sorting characters, ignoring numbers
 

Assuming you have only 1 number in front of the address

1. insert a column to the right and place the formula; which will return the
text value alone.
=IF(ISNUMBER(--LEFT(A1,1)),MID(A1,FIND(" ",A1)+1,100),A1)

2.Select both columns and sort by the inserted column
3. You can either keep this column hidden or delete after use.

If this post helps click Yes
---------------
Jacob Skaria


"Barto9729" wrote:

I have a list with a column of street addresses. I would like to sort by
street but ignore the house number.

for example is want the results to be:

123 Pine
456 Pine
145 Ross
668 Ross

NOT:

123 Pine
145 Ross
456 Pine
668 Ross

Any ideas?

Sorting characters, ignoring numbers
 

Here's one a bit like Glenn's solution.

In an adjacent column, (if that can work) enter and pull down...

=MID(C12,FIND(" ",C12)+1,1)

Select this new column first along with the old column and sort.
Flush the new column.

HTH
Regards,
Howard

"Barto9729" wrote in message
...
I have a list with a column of street addresses. I would like to sort by
street but ignore the house number.

for example is want the results to be:

123 Pine
456 Pine
145 Ross
668 Ross

NOT:

123 Pine
145 Ross
456 Pine
668 Ross

Any ideas?

Sorting characters, ignoring numbers
 

Brilliant!
I am so thrilled to be able to access the minds of such excellent formula masters

Sorting characters, ignoring numbers
 

Is it possible to sort this way without using extra columns? For instance I have a spreadsheet with 256 columns and a header row with between 10 and 30 columns that need sorting (ignoring the number at the beginning ... or first 2 characters) below each. I am thinking I will need VBA to do this.