Sort Need

[wgd.roaming]

A B C
Row

1 206 S. Harbor Dr. 206 S. Harbor Dr.


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

First equ immediately above is in B1 =LEFT(A1,FIND(" ",A1)-1)
Second is in C1. =TRIM(MID(A1,FIND(" ",A1)+1,255))

A1 is address in column of addresses to be sorted.

Equ starting: =LEFT pulls out the street address number
Equ starting: =TRIM pulls balance.

Problem: In this example, Sort places C1 in rows
starting with letter S.

Would like to put this row in the H section.

The 'S' could be put in the B1 cell, that is: 206 S

Other examples: 1234 N. Washington. The above puts the N. Washington
in the N section, prefer, the W section.

It is those single letters in front of the 'main' street address name
that is messing this up.

Equations above were greatfully supplied about 3 yrs ago by a member
of this group. Back then, the S, for South, for example was not part
of the problem.

Pls, how is this done?
And Thank You, Wayne
Sarasota, FL

 

[Ron Rosenfeld]

A B C
Row

1 206 S. Harbor Dr. 206 S. Harbor Dr.


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

First equ immediately above is in B1 =LEFT(A1,FIND(" ",A1)-1)
Second is in C1. =TRIM(MID(A1,FIND(" ",A1)+1,255))

A1 is address in column of addresses to be sorted.

Equ starting: =LEFT pulls out the street address number
Equ starting: =TRIM pulls balance.

Problem: In this example, Sort places C1 in rows
starting with letter S.

Would like to put this row in the H section.

The 'S' could be put in the B1 cell, that is: 206 S

Other examples: 1234 N. Washington. The above puts the N. Washington
in the N section, prefer, the W section.

It is those single letters in front of the 'main' street address name
that is messing this up.

Equations above were greatfully supplied about 3 yrs ago by a member
of this group. Back then, the S, for South, for example was not part
of the problem.

Pls, how is this done?
And Thank You, Wayne
Sarasota, FL

You need to split up the street part of the address.

From the data you have presented, it is possible that all you need to do is test for the presence of a single letter followed by a digit or a space, at the beginning of that segment.
So perhaps something like:

C1:
=IF(OR(MID(TRIM(MID(A1,FIND(" ",A1),99)),2,1)=" ",
MID(TRIM(MID(A1,FIND(" ",A1),99)),2,1)="."),
LEFT(TRIM(MID(A1,FIND(" ",A1),99)),1),"")

D1:
=IF(OR(MID(TRIM(MID(A1,FIND(" ",A1),99)),2,1)=" ",MID(
TRIM(MID(A1,FIND(" ",A1),99)),2,1)="."),MID(TRIM(MID(
A1,FIND(" ",A1),99)),FIND(" ",TRIM(MID(A1,FIND(
" ",A1),99)))+1,99),TRIM(MID(A1,FIND(" ",A1),99)))

And sort on column D; then on Column C

If you need to test for more than just a single letter following the first space, due to the nature of your data, we should probably devise a VBA solution.

 

[wgd.roaming]

You need to split up the street part of the address.

From the data you have presented, it is possible that all you need to do is test for the presence of a single letter followed by a digit or a space, at the beginning of that segment.
So perhaps something like:

C1:
=IF(OR(MID(TRIM(MID(A1,FIND(" ",A1),99)),2,1)=" ",
MID(TRIM(MID(A1,FIND(" ",A1),99)),2,1)="."),
LEFT(TRIM(MID(A1,FIND(" ",A1),99)),1),"")

D1:
=IF(OR(MID(TRIM(MID(A1,FIND(" ",A1),99)),2,1)=" ",MID(
TRIM(MID(A1,FIND(" ",A1),99)),2,1)="."),MID(TRIM(MID(
A1,FIND(" ",A1),99)),FIND(" ",TRIM(MID(A1,FIND(
" ",A1),99)))+1,99),TRIM(MID(A1,FIND(" ",A1),99)))

And sort on column D; then on Column C

If you need to test for more than just a single letter following the first space, due to the nature of your data, we should probably devise a VBA solution.

PERFECT! Woke up this morning to add that separating into two
columns would work fine. But how? That is what you did! LONG
formula, but it works and I am appreciative! Thank You

Wayne
Sarasota, Fl

 

[Ron Rosenfeld]

PERFECT! Woke up this morning to add that separating into two
columns would work fine. But how? That is what you did! LONG
formula, but it works and I am appreciative! Thank You

Wayne
Sarasota, Fl

Glad to help. Thanks for the feedback

 

[wgd.roaming]

Glad to help. Thanks for the feedback

Bears repeating, your sort equations worked well, very well. Here is
a little extension, need, pls.

Nbrs tend to sort with greatest nbr of places first, that is,

1023
330

How can this be reversed, that is, sorting by address name works, A-Z.
But the nbr portion will be:

1023 Harbor Road
330 Harbor Road

Wud like:

330 Harbor Road
1023 Harbor Road

Pls see last post for what is in the Nbr column.


Thank You

Wayne, Sarasota, FL

 

[Ron Rosenfeld]

How can this be reversed, that is, sorting by address name works, A-Z.
But the nbr portion will be:

1023 Harbor Road
330 Harbor Road

Wud like:

330 Harbor Road
1023 Harbor Road

You just need to change your formula for col B sot the number is returned as a number and not as a text string:

B1: =--LEFT(A1,FIND(" ",A1)-1)

The double unary will coerce the result to a number

 

[wgd.roaming]

You just need to change your formula for col B sot the number is returned as a number and not as a text string:

B1: =--LEFT(A1,FIND(" ",A1)-1)

The double unary will coerce the result to a number

Thank You, Ron.

Wayne in SRQ