Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Aug 2003
    Location
    amsterdam, Netherlands
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    splitting a columm (excel 2003)

    hi everyone

    I have a (pretty much messy) column with adresses with combined adress and number.
    I want to seperate the number from the adress.
    the problem is that some adresses consist of multiple words ( so there are multiple spaces in the field)
    so the wizard " text to colum" doenst help
    also the numbers vary from one to 4 digits
    for instance. "van der valk straat 320" or "damrak 1"
    so trimming right also doesn't work

    the only I thing I think could work is to find the first space from RIGHT to Left(!) because there is always a space in between number and adress
    but I can't find any function in excel to work with that.
    does anyone know a solution or a work around?

    thanks ahead!

    diederik

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: splitting a columm (excel 2003)

    If the house number is always the last "word" in the address, you can use the formulas below. But they will fail for situations like these:

    Herensingel 13 A
    Telderskade 3 huis

    since there is a space in what constitutes the "number". If the addresses are written like this:

    Herensingel 13A
    Telderskade 3-huis

    it will be OK. Say that the addresses are in column A, starting with A1. Enter this formula in B1 to get the "street" part:

    =LEFT(A1,MAX((MID(A1,ROW(1:100),1)=" ")*ROW(1:100))-1)

    and confirm with Ctrl+Shift+Enter (it is an array formula). Next, enter this formula in C1 to get the "number" part:

    =MID(A1,MAX((MID(A1,ROW(1:100),1)=" ")*ROW(1:100))+1,100)

    and confirm with Ctrl+Shift+Enter. Finally, fill down as far as needed. See attached example.

  3. #3
    Lounger
    Join Date
    Aug 2003
    Location
    amsterdam, Netherlands
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: splitting a columm (excel 2003)

    Hans,
    you're great!
    thank you very much!

    Diederik

  4. #4
    Lounger
    Join Date
    Aug 2003
    Location
    amsterdam, Netherlands
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: splitting a columm (excel 2003)

    Hi Hans,

    I was a little to early with my reply yesterday.

    I still have a question about the formula
    I don't completely understand what the ROW part does in the the formula
    and something goes wrong when I paste the formula down
    see example.

    how does that ROW command exactly work?
    (I do not quite understand the Array thing in this situation)
    thanks

    diederik

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: splitting a columm (excel 2003)

    You can fill down the formulas if you make the reference to 1:100 absolute: $1:$100.

    The idea behind these array formulas is from Using Array Formulas in Excel OFC-10 by Bob Umlas.

    ROW($1:$100) evaluates to the array (1, 2, 3, ..., 100).
    MID(A1,ROW($1:$100),1) takes A1 apart into individual characters, for example ("D", "a", "m", "r", "a", "k", " ", "1", "", "", ...)
    MID(A1,ROW($1:$100),1)=" " compares the characters to a space, resulting in an array of TRUE/FALSE: (FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, ...)
    This is multiplied by the character index, using the fact that FALSE = 0 and TRUE = 1:
    (MID(A1,ROW($1:$100),1)=" ")*ROW($1:$100) results in (0, 0, 0, 0, 0, 0, 7, 0, 0, ...)
    The non-zero entries correspond to spaces in the original string.
    MAX((MID(A1,ROW($1:$100),1)=" ")*ROW($1:$100)) returns the index of the last space in the string, in this example 7.

    For the street part (column [img]/forums/images/smilies/cool.gif[/img], we take everything to the left of this character, for the number part (column C), everything to the right of it.

  6. #6
    Lounger
    Join Date
    Aug 2003
    Location
    amsterdam, Netherlands
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: splitting a columm (excel 2003)

    Hi Hans,

    Thanks for the explanation.
    That link to explaning array functions is also very usefull!!

    Greetings again.

    Diederik

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: splitting a columm (excel 2003)

    Here's an alternative way without arrays.


    Ken

  8. #8
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: splitting a columm (excel 2003)

    Here is the non-array formula, even another space behind the "number" still can do the work.

    Herensingel 13 A
    Telderskade 3 huis B

    Enter this formula in cell B1 to get the "street" part:

    =LEFT(A1,MIN(FIND("~",SUBSTITUTE(A1,{0;1;2;3;4;5;6 ;7;8;9},"~")&"~"))-1)

    Then, in cell C1 enter this formula to get the "number" part:

    =SUBSTITUTE(A1,B1,"")

    Regards

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: splitting a columm (excel 2003)

    Welcome to Woody's Lounge. Nice formulas! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    If the street name contains a number, the result is still incorrect, but no need to worry. The Dutch postal service uses an application to split addresses; it contains algorithms to handle most cases, but falls back on a list of exceptions for the really difficult addresses.

  10. #10
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: splitting a columm (excel 2003)

    Thanks Hans,

    Just play a way in looking for the digit position, in lieu of last space position

    Sometimes I found that it is danger to use the formula in look for last space postion, if the raw source has not cleaned ( that is the data with some hidden space or nonprintable characters )



    The formula can be modified, if the street name contains a number

    Example:
    Heren2singel 13 A

    B1 :
    =LEFT(A1,SMALL(FIND("~",SUBSTITUTE(A1,{0;1;2;3;4;5 ;6;7;8;9},"~")&"~"),2)-1)

    Regards

Posting Permissions

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