Excel Sort?

[Templar]

Below is a sample of the portion of a column on an excel sheet.
I would like to put everything to the right of the number in a separate
column.
such as, everything to the right of 6346, 6510 and so forth and trickier
ones like 7140-44

Can anyone explain to me how I would do this? Thanks.

6346 W. 13th Street
6510 W. 16th Street
6516 W. 16th Street
6519 W. 16th Street
6706 W. 16th Street
6808 W. 16th Street
7140-44 W. 16th Street
6445-10 W. 18th Street
6501 W. 18th Street
6446 W. 19th Street
6502-04 W. 19th Street
6401 W. 23rd Street
6631 W. 23rd Street
6243 W. 26th Street
6301 W. 26th Street
6305 W. 26th Street
6310 W. 26th Street
6316 W. 26th Street
6320 W. 26th Street
6328 W. 26th Street
6337 W. 26th Street
6818 W. 26th Street
6818 W. 26th Street
7132 W. 26th Street
7136 W. 26th Street
6412 W. 27th Street
6546 W. 28th Street
6737 W. 31st Street
6405-07 W. 34th Street
6411 W. 34th Street
6923 W. 34th Street
6201 W. Cermak Road
6201 W. Cermak Road
6239 W. Cermak Road
6241 W. Cermak Road
6301 W. Cermak Road
6311 W. Cermak Road
6323 W. Cermak Road
6513 W. Cermak Road
6539 W. Cermak Road
6543 W. Cermak Road
6617 W. Cermak Road
6705 W. Cermak Road
6712 W. Cermak Road
6717 W. Cermak Road
6732 W. Cermak Road
6732 W. Cermak Road
6735 W. Cermak Road
6930 W. Cermak Road
6930 W. Cermak Road
6931 W. Cermak Road
6947 W. Cermak Road
7000 W. Cermak Road
7000 W. Cermak Road
7038 W. Cermak Road
7120 W. Cermak Road
1846 S. Clarence Avenue
1247 S. Clinton Avenue
3201-3-5-7 S. Clinton Avenue
1647 S. Euclid Avenue
1903 S. Euclid Avenue
1923 S. Euclid Avenue
1951 S. Euclid Avenue
1915 S. Grove Avenue
1925-31 S. Grove Avenue
3131 S. Grove Avenue

 

[Kevin B]

Insert 2 helper columns to the right of your address column and use the
following formula in the first column to get the numeric prefix from the
address (changing the cell address as needed):

=LEFT(A1,FIND(" ",A1,1))

In the second column, use the following formula to get the street suffix
portion of the address:

=RIGHT(A1,LEN(A1)-(FIND(" ",A1,1)))

Copy the 2 formula cells down as far as needed. Copy both formula columns
and the click Edit in the menu and select PASTE SPECIAL and in the following
dialog box click the VALUES option button and click OK.

You can now delete the original address column to complete the process.

 

[David Biddulph]

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

 

[Jim Cone]

With your data starting in B5, enter this formula in C5 and fill down...
=RIGHT(B5,LEN(B5) -FIND(" ",B5,1))
It finds the first space and returns the text after that character.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - two dozen ways to sort with "Special Sort")



"Templar"
wrote in message
Below is a sample of the portion of a column on an excel sheet.
I would like to put everything to the right of the number in a separate
column.
such as, everything to the right of 6346, 6510 and so forth and trickier
ones like 7140-44

Can anyone explain to me how I would do this? Thanks.

6346 W. 13th Street
6510 W. 16th Street
6516 W. 16th Street
6519 W. 16th Street
6706 W. 16th Street
6808 W. 16th Street
7140-44 W. 16th Street
6445-10 W. 18th Street
6501 W. 18th Street
6446 W. 19th Street
-snip-