Separate numeric and alpha

I have an excel worksheet with addresses in the form of "1234 Some St."
I would like to sort by street name.

Any solution?

Thanks in advance

Chuck

A simple method is to break out the addresses into separate columns then sort
on the street name column.

Select the column with the address(make sure you have a few empty columns to
the right of the data column).

Data>Text to Columns>Delimited by space and finish.

Select all the columns and sort on street name.

Best to leave them split out that way. Most apps you export to like them in
separate cells.

You can stick them back into one cell after the sort if you wish.

=A1 & " " and B1 & " " & C1


Gord Dibben Excel MVP



On Mon, 13 Jun 2005 17:07:09 -0700, "Chuck Davis" <newsgroup at anthemwebs dot
com> wrote:

>I have an excel worksheet with addresses in the form of "1234 Some St."
>I would like to sort by street name.
>
>Any solution?
>
>Thanks in advance
>

I personally prefer Gord's suggestion, but just as an alternative,........in
a helper column put........

=MID(A1,FIND(" ",A1,1)+1,99)

Vaya con Dios,
Chuck, CABGx3




"Chuck Davis" <newsgroup at anthemwebs dot com> wrote in message
news:##[email protected]...
> I have an excel worksheet with addresses in the form of "1234 Some St."
> I would like to sort by street name.
>
> Any solution?
>
> Thanks in advance
>
>

To add to Gord's suggestion, when using TTC, you can leave the original data
column *untouched*, therefore eliminating the need to "rejoin" the
separated, "sorting" columns.

In the third window of the TTC wizard, there's a "Destination" box, with the
address defaulted to that of the original column.
Simply change that to an adjoining column, and the original will remain as
is.

Then just include the original in the sort range, and you'll then have a
choice to keep or discard all or just some of those separated columns.

BTW, you should follow Gord's advice and use the "split" data columns ...
that's just good "practice".
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:[email protected]...
> Chuck
>
> A simple method is to break out the addresses into separate columns then
sort
> on the street name column.
>
> Select the column with the address(make sure you have a few empty columns
to
> the right of the data column).
>
> Data>Text to Columns>Delimited by space and finish.
>
> Select all the columns and sort on street name.
>
> Best to leave them split out that way. Most apps you export to like them
in
> separate cells.
>
> You can stick them back into one cell after the sort if you wish.
>
> =A1 & " " and B1 & " " & C1
>
>
> Gord Dibben Excel MVP
>
>
>
> On Mon, 13 Jun 2005 17:07:09 -0700, "Chuck Davis" <newsgroup at anthemwebs
dot
> com> wrote:
>
> >I have an excel worksheet with addresses in the form of "1234 Some St."
> >I would like to sort by street name.
> >
> >Any solution?
> >
> >Thanks in advance
> >
>

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:[email protected]...
> Chuck
>
> A simple method is to break out the addresses into separate columns then
> sort
> on the street name column.
>
> Select the column with the address(make sure you have a few empty columns
> to
> the right of the data column).
>
> Data>Text to Columns>Delimited by space and finish.
>
> Select all the columns and sort on street name.
>
> Best to leave them split out that way. Most apps you export to like them
> in
> separate cells.
>
> You can stick them back into one cell after the sort if you wish.
>
> =A1 & " " and B1 & " " & C1
>
>
> Gord Dibben Excel MVP
>
>
>
> On Mon, 13 Jun 2005 17:07:09 -0700, "Chuck Davis" <newsgroup at anthemwebs
> dot
> com> wrote:
>
>>I have an excel worksheet with addresses in the form of "1234 Some St."
>>I would like to sort by street name.
>>
>>Any solution?
>>
>>Thanks in advance
>>
>
Thanks again. It worked, except I can never delete the columns with the
numeric and alpha data. Its not a real problem.

Chuck

See RD's post for further refinements to TTC which I neglected to point out.

Also, if you have gone with the "splitting" and want the data placed back in
one cell using the formula I posted, just select the column and Copy>Paste
Special>Values>OK>Esc.

Then delete the original split-out columns.


Gord


On Mon, 13 Jun 2005 20:14:43 -0700, "Chuck Davis" <newsgroup at anthemwebs dot
com> wrote:

>Thanks again. It worked, except I can never delete the columns with the
>numeric and alpha data. Its not a real problem.