|
Excel Sort?
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 -- Templar |
Excel Sort?
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. -- Kevin Backmann "Templar" wrote: 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 -- Templar |
Excel Sort?
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
-- David Biddulph "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 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 -- Templar |
Excel Sort?
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- -- Templar |
| All times are GMT +1. The time now is 12:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com