Sorting characters, ignoring numbers

[Barto9729]

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?

 

[Glenn]

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

 

[Barto9729]

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]

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.

 

[Jacob Skaria]

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

 

[L. Howard Kittle]

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

 

[discombobolate]

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

 

[michaelkgraham]

Is it possible to sort this way without using extra columns? For instance Ihave 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.