+ Reply to Thread
Results 1 to 10 of 10

Split the number from an adress ? (ie. Road of Liberty 17)

  1. #1
    Registered User
    Join Date
    09-18-2008
    Location
    Switzerland
    MS-Off Ver
    Office 2003
    Posts
    82

    Split the number from an adress ? (ie. Road of Liberty 17)

    Hi,

    I have tons of adresses formatted in one cell like this: "Road of Liberty 14",
    I need 1 cell for the adress and 1 for the house number
    "Road of Liberty" "14".
    The problem with splitting is that there are very often spaces in the adress, so Excel will split it the following way: "Road" "of" Liberty" "14"...
    I couldn't figure out how to simply extract all numbers out of the text and paste it in the next cell.
    Since it is a huge database and I will have to repeatedly do it again I would prefer not to do it manually.
    Thanks in advance for any help !

    Simon
    Last edited by oldchippy; 10-23-2008 at 04:18 PM.

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Hi,

    Welcome to the forum, you could use Data > Text to columns and use delimited and select how many columns you require
    Attached Images Attached Images
    Last edited by oldchippy; 09-18-2008 at 06:02 AM.
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    can you give some more example addresses.
    can the address be high street 14a or is it always a number at the end?
    what is the maximum length of the number?

    I am thinking as per the example you want each address spilt in 2?
    Is it always the last space in a string tthat is the point it should be split?

    Regards

    Dav

  4. #4
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    well perhaps if the address is in a1

    in b1
    Please Login or Register  to view this content.
    and in c1
    Please Login or Register  to view this content.
    Does that help

    Regards

    Dav

  5. #5
    Registered User
    Join Date
    09-18-2008
    Location
    Switzerland
    MS-Off Ver
    Office 2003
    Posts
    82
    Thanks for the answer, first solution doesn't work for me because the addresses don't have a fix lenght, their format varies very much.
    In the few cases that the housenumber would be 15a, or similar I can manually change it... but the house numbers can be 1, 17 or 119.
    And yes, it is the last space where it should be split!
    I cannot directly copy your formula Dav, because my excel is german, but I will try to decipher the logic behind it, thanks !
    I would appreciate any new answers as well !

  6. #6
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099

  7. #7
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Here's an alternative if you need one

    Name of road

    =TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)-1)))

    House number

    =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10))

  8. #8
    Registered User
    Join Date
    09-18-2008
    Location
    Switzerland
    MS-Off Ver
    Office 2003
    Posts
    82
    Thanks, I have succesfully translated these 2 formulas into german but it turns up with #name?
    Any help with that ?

  9. #9
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    does this help?


    Regards

    Dav
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    09-18-2008
    Location
    Switzerland
    MS-Off Ver
    Office 2003
    Posts
    82
    Excellent, that helps indeed, have been trying for an hour or so to solve this problem now, seems like a lot of folks on the internet have a similar problem!
    Thanks again!

    Simon

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. number puzzle
    By mkron in forum Excel General
    Replies: 14
    Last Post: 01-28-2007, 12:13 AM
  2. Determining required number of contacts
    By Bobcalling in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2007, 01:27 PM
  3. Replies: 4
    Last Post: 01-02-2007, 07:50 AM
  4. Counting a number series plus row delete
    By Vlad999 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-08-2006, 12:53 AM
  5. Complicated number searching
    By Handyy in forum Excel General
    Replies: 5
    Last Post: 11-25-2006, 11:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1