none
How to separate names and address in Excel RRS feed

  • Question

  • I am copying data from a webpage and when I paste it into Excel, the data is all in column A. The data looks like

                             

                               A Connected Heart 16116 Vick Rd Gulfport MS 228-831-4242

                                Alden Pointe 2 2 Courtland Dr. Hattiesburg MS 601-296-9711

                                Alternative Personal Care Home 6816 Washington Ave Ocean Springs MS 228-872-7022

    I would like to be able to quickly separate the name, address, city and phone number in a excel spreadsheet.

    I have tried the text to column without and success.

    Please any help or advice would be greatly appreciated.

    Thanks


    JC IHC

    Friday, August 1, 2014 4:08 PM

Answers

All replies

  • Re:  separating name and address

    There is no one size fits all solution and there may be no solution, but...

    You can split off the last 12 characters (telephone number) using...
       =RIGHT(B3,12)
    You can split off all text to the left of the first number (possibly the name) using...
       =TRIM(LEFT(B3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B3&"0123456789"))-1))
    And see Chip Pearson's contribution at...
       http://www.cpearson.com/Excel/FirstLast.htm

    You could also hire a temp to do it manually.
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    • Edited by James Cone Monday, October 31, 2016 6:30 PM
    Friday, August 1, 2014 5:37 PM
  • I can see a rule within your samples, run the macro below.

    Andreas.

    Sub Test()
      Dim Data, i As Long, j As Long, a As Long, b As Long
      Dim Line, Temp
      Dim Part
      
      'Get the data
      Data = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
      'Add 2 columns
      ReDim Preserve Data(1 To UBound(Data), 1 To 3)
      For i = 1 To UBound(Data)
        'Prepare the line
        Line = Trim(Data(i, 1))
        Do While InStr(Line, "  ") > 0
          Line = Replace(Line, "  ", " ")
        Loop
        If Line = "" Then GoTo Skip
        'Split into words
        Temp = Split(Line)
        'Find a numeric word
        a = 0
        Do While Not IsNumeric(Temp(a))
          a = a + 1
          If a > UBound(Temp) Then GoTo Skip
        Loop
        'Go further until we find a normal word
        Do While IsNumeric(Temp(a))
          a = a + 1
          If a > UBound(Temp) Then GoTo Skip
        Loop
        'Part found, search for a word that begins with a number
        b = a
        a = a - 1
        Do While Not IsNumeric(Left(Temp(b), 1))
          b = b + 1
          If b > UBound(Temp) Then GoTo Skip
        Loop
        b = b - 1
        'Part found, copy the parts into the columns
        ReDim Part(b To UBound(Temp))
        For j = LBound(Part) To UBound(Part)
          Part(j) = Temp(j)
        Next
        Data(i, 3) = Join(Part)
        ReDim Part(a To b - 1)
        For j = LBound(Part) To UBound(Part)
          Part(j) = Temp(j)
        Next
        Data(i, 2) = Join(Part)
        ReDim Part(0 To a - 1)
        For j = LBound(Part) To UBound(Part)
          Part(j) = Temp(j)
        Next
        Data(i, 1) = Join(Part)
    Skip:
      Next
      
      'Add a sheet and store the result
      Sheets.Add
      Range("A1").Resize(UBound(Data), UBound(Data, 2)) = Data
      Columns("A:C").AutoFit
    End Sub
    

    Friday, August 1, 2014 6:05 PM
  • I have not used a macro before. It worked.

    Thanks


    JC IHC

    • Marked as answer by JC IHC Friday, August 1, 2014 6:35 PM
    Friday, August 1, 2014 6:35 PM