Announcement

Collapse
No announcement yet.

Splitting Address Details Into Several Columns

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    Comment


    • #3
      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?

      Comment


      • #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?

        Comment


        • #5
          Re: Splitting Address Details Into Several Columns

          Hi,

          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, 12:48.
          Kris

          ExcelFox

          Comment


          • #6
            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

            Comment

            Working...
            X