Splitting Cell Data?

OK here's a great one. Check this out. I have a simple one page spreadsheet of addresses. My client wants me to sort/filter the data so that it is sorted by street name first, then by address number. The problem is that the spreadsheet has 30,000 entries and the Address Column contains the street number and street name (i.e. 12345 Park Drive Blvd. Apt #345). So my question is: does anyone know how I can separate the street number portion of the address from the street name portion of the address to be put into two separate columns so I can sort them?
Thanks in advance,
Dax

Hi
if the street number is always at the beginning of your data use a
helper column to extract the streetname and enter the following formula
=MID(A1,FIND(" ",A1)+1,255)
and copy this down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany


Dax Arroway wrote:
OK here's a great one. Check this out. I have a simple one page
spreadsheet of addresses. My client wants me to sort/filter the data
so that it is sorted by street name first, then by address number.
The problem is that the spreadsheet has 30,000 entries and the
Address Column contains the street number and street name (i.e. 12345
Park Drive Blvd. Apt #345). So my question is: does anyone know how
I can separate the street number portion of the address from the
street name portion of the address to be put into two separate
columns so I can sort them? Thanks in advance,
Dax

You could try using "TextToColumns".

Make note of the column letter at the end of the data list.
Select the address column, then:
Data TextToColumns Next,
Click in "Space", then Next.

Now, count the number of columns that are displaying the separations.
Make sure that you have that many *empty* contiguous columns at the end of
the data list.
In the "Destination" window, enter the empty column at the end of the list,
Then Finish.

You now have your addresses broken up into several columns.
You can now choose any column you wish as sort key 1, and any other as sort
key 2 ... and so on.

When your finished sorting, simply delete these "helper" columns if you
wish.
--
HTH,

RD

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



"Dax Arroway" Dax wrote in message
...
OK here's a great one. Check this out. I have a simple one page
spreadsheet of addresses. My client wants me to sort/filter the data so
that it is sorted by street name first, then by address number. The problem
is that the spreadsheet has 30,000 entries and the Address Column contains
the street number and street name (i.e. 12345 Park Drive Blvd. Apt #345).
So my question is: does anyone know how I can separate the street number
portion of the address from the street name portion of the address to be put
into two separate columns so I can sort them?
Thanks in advance,
Dax