 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / May 2008Split a street address field apart.
How do I split a street address field apart? Field will have 2-3-4-5 digits, followed by a space then a series of alphas. i.e. 837 Third Avenue -or- 6 Street West. In a column of these address fields, wish to create two fields: the 2-5 numericals only in one, and the remaining alphas in another. Not VBA-oriented, please, what might be the best, and simplest for my mind, way to achieve this? Using Office 2007. Kind Regards, Wayne Dengel Try this: A1: (address text....eg 837 Third Avenue) The number B1: =LEFT(A1,FIND(" ",A1)-1) The street C1: =TRIM(MID(A1,FIND(" ",A1)+1,255)) Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > How do I split a street address field apart? Field will have 2-3-4-5 > digits, followed by a space then a series of alphas. i.e. 837 Third [quoted text clipped - 10 lines] > Kind Regards, > Wayne Dengel Will give is a sincere try. . . and Thank You! Wayne > Try this: > A1: (address text....eg 837 Third Avenue) [quoted text clipped - 29 lines] >> Kind Regards, >> Wayne Dengel Worked perfectly - Thank You. What annoys me is that at one time I could think this through "all by myself". Times have changed! Kind Regards, Wayne The post that followed your, will work through that one also - maybe jog the brain into thinking again! > Will give is a sincere try. . . and Thank You! > Wayne [quoted text clipped - 32 lines] >>> Kind Regards, >>> Wayne Dengel Ron: I use the two equations noted below, those that you suggested. They work very well. However, Here is a problem: after splitting out number(s) from the street name, I then sort the whole table, ranking by street number. What happens is the following: lines (rows) sorted by the street number field end up as follows: 1245 2456 345 4567 6789 750 where the line with 345 (which is numerically lower than 1245) should preceed the line with 1245; likewise for 750. Preferred ranking would be: 345 750 1245 2456 4567 6789 What do I need to do to effect this change? Thank You! Wayne Dengel > Try this: > A1: (address text....eg 837 Third Avenue) [quoted text clipped - 29 lines] >> Kind Regards, >> Wayne Dengel The numbers are Text due to the splitting out with a formula. Format all to General, copy an empty cell. Select the range to change then Paste Special>Add>OK>Esc. Now try the sort. Gord Dibben MS Excel MVP >Ron: > [quoted text clipped - 60 lines] >>> Kind Regards, >>> Wayne Dengel You folks are the greatest! An items as old as this one is - your quick response is greatly appreciated. Wayne > The numbers are Text due to the splitting out with a formula. > [quoted text clipped - 72 lines] >>>> Kind Regards, >>>> Wayne Dengel Does this help? http://www.uwec.edu/Help/Excel07/ws-separate.htm HTH, JP > How do I split a street address field apart? Field will have 2-3-4-5 > digits, followed by a space then a series of alphas. i.e. 837 Third [quoted text clipped - 10 lines] > Kind Regards, > Wayne Dengel Another good solution. Appreciated. I did this kind of thing a few, actually many years ago - gray matter could not pull from deep within the solution I used back then. Does this help? http://www.uwec.edu/Help/Excel07/ws-separate.htm HTH, JP On Mar 19, 6:37 pm, "WGD" <wgd.roam...@verizon.net> wrote: > How do I split a street address field apart? Field will have 2-3-4-5 > digits, followed by a space then a series of alphas. i.e. 837 Third [quoted text clipped - 11 lines] > Kind Regards, > Wayne Dengel
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.