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?

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

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

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

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?

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?

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

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.