Sorting characters, ignoring numbers

  • Thread starter Thread starter Barto9729
  • Start date Start date
B

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?
 
Barto9729 said:
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?
Click to expand...


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)?
 
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.
 
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
 
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
 
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 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.
 
Back
Top