Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Splitting Address Details Into Several Columns

  1. #1
    Join Date
    18th October 2006
    Posts
    4

    Splitting Address Details Into Several Columns

    I'm in need to split 1 column (address details) into several columns (for ex. address, city, postcode, country)
    I can't use the "Text to Column" function as some address will split into 5 part, some split into 6/7/8.

    Is there any way I could do this automatically as there're a lot of entries in my spreadsheet.

    Example of the data need to be split attached.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2004
    Location
    Berkshire UK
    Posts
    426

    Re: Splitting Address Details Into Several Columns

    Hi

    Try using Text to Columns and amending the delimiter to "/". It seemed OK to me when I used this method on your sample

    Robert

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    Re: Splitting Address Details Into Several Columns

    I assume the forward slash is where the split should occur. If so then why can't you use Text to columns.. with the / as the delimiter. If not, what is the delimiter?

  4. #4
    Join Date
    18th October 2006
    Posts
    4

    Re: Splitting Address Details Into Several Columns

    The data is spread across different columns, I need the data to be underneath the correct headers example UK in the country column, Postcodes in the postcode column and so on. I need to know if there is a formulised way of doing this or if I have to sort by individual columns and copy across the relevant data, deleting after it has been moved?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Splitting Address Details Into Several Columns

    Hi,

    VB:
    Sub TestIt() 
        Dim i   As Long, j  As Long 
        Dim x, v 
        i = Range("A" & Rows.Count).End(xlUp).Row 'determines last row
         
        For j = 2 To i 
             'checks if there is a space within the first 20 letters
            If InStr(1, Left(Cells(j, 1), 20), " ") > 0 Then 
                 'if so extracts the first word. This is because you have some address like 4/5,27/8 etc.
                v = Left(Cells(j, 1), InStr(1, Cells(j, 1), " ") - 1) 
                 'splits each word "/" as a delimiter after the first word
                x = Split(Mid(Cells(j, 1), InStr(1, Cells(j, 1), " "), 255), "/") 
            Else 
                v = Empty 
                x = Split(Cells(j, 1), "/") 
            End If 
            Select Case UBound(x) 'number of "/"s
            Case 3 
                Cells(j, 2) = v & x(0): Cells(j, 7) = x(1): Cells(j, 8) = x(2): Cells(j, 9) = x(3) 
            Case 4 
                Cells(j, 2) = v & x(0): Cells(j, 6) = x(1): Cells(j, 7) = x(2): Cells(j, 8) = x(3) 
                Cells(j, 9) = x(4) 
            Case 5 
                Cells(j, 2) = v & x(0): Cells(j, 5) = x(1): Cells(j, 6) = x(2): Cells(j, 7) = x(3) 
                Cells(j, 8) = x(4): Cells(j, 9) = x(5) 
            Case 6 
                Cells(j, 2) = v & x(0): Cells(j, 4) = x(1): Cells(j, 5) = x(2): Cells(j, 6) = x(3) 
                Cells(j, 7) = x(4): Cells(j, 8) = x(5): Cells(j, 9) = x(6) 
            Case 7 
                Cells(j, 2) = v & x(0): Cells(j, 3) = x(1): Cells(j, 4) = x(2): Cells(j, 5) = x(3) 
                Cells(j, 6) = x(4): Cells(j, 7) = x(5): Cells(j, 8) = x(6): Cells(j, 9) = x(7) 
            Case Else 
            End Select 
        Next j 
    End Sub 
    
    
    I think rest of the code is self explanatory.

    HTH
    Last edited by Krishnakumar; October 19th, 2006 at 12:48.

  6. #6
    Join Date
    18th October 2006
    Posts
    4

    Re: Splitting Address Details Into Several Columns

    Thanks a lot KrishnaKumar!
    That works great!

    Is there anychance that you could put some comment on it?
    I'm lost in there :D

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Automatically Fill Address Details
    By canadian_diva in forum Excel General
    Replies: 1
    Last Post: May 24th, 2007, 20:08
  2. Data Splitting in Columns
    By Rajat.Karir in forum Excel General
    Replies: 5
    Last Post: September 2nd, 2006, 16:30
  3. Split address details
    By thoemmes in forum Excel General
    Replies: 27
    Last Post: January 20th, 2006, 01:01
  4. Splitting Address In Cell
    By vith01 in forum Excel General
    Replies: 7
    Last Post: June 23rd, 2005, 17:27
  5. Splitting up text in 2 columns
    By EVelthoven in forum Excel General
    Replies: 4
    Last Post: May 22nd, 2003, 16:23

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
  •  
porno