Splitting Cell Data?

[Guest]

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

 

[Frank Kabel]

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

 

[Ragdyer]

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

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?