Split address field in Excel

Question

I have an excel file with cells with addresses.

3 different data examples:

 123 Main Street/n Apartment2 /n New York, NY 10001
 123 Main Street/n New York, NY 10001
 123 Main Street

I would like to split it into three separate fields. However, as the above data shows, some will have 0 occurrences of /n, some will have 2 occurrences. and some will have 3 occurrences.

I can handle one occurrence, with: =LEFT(E6, SEARCH(" /n",E6,1)-2) =RIGHT(Export!E6,LEN(Export!E6)- SEARCH("/n",Export!E6,1)-1)

However, my formula starts breaking down when there are variable number of /n

Any suggestions?

You do not need formula - Text to columns will do the job perfectly) — Peter L., Feb 07 '13 at 19:34
unforunately, I could not get text to columns working, it only accepts on character not two "/n" Keep in mind that /n is actually two characters and not a new line break. — user2012677, Feb 07 '13 at 19:46
Replace "/n" to any single character which is NOT in your data, e.g. @#$^~ - and go to the above) Replace All is available via Ctrl-H. — Peter L., Feb 07 '13 at 19:48

score 5 · Answer 1 · answered Feb 07 '13 at 20:18

5

Solution worth an answer:

Replace "/n" to any single character which is NOT in your data, e.g. @#$^~ - and use Text to Columns.

Replace All is available via press CTRL+H.

If you're not sure the character is in your data - check via search before replacement.

answered Feb 07 '13 at 20:18

Peter L.

7,276
5
34
53

+ 1 for the non VBA Method :) – Siddharth Rout Feb 07 '13 at 20:21

score 3 · Answer 2 · answered Feb 07 '13 at 20:20

Here is another method using VBA

Option Explicit

Sub Sample()
    Dim MyArray() As String
    Dim ws As Worksheet
    Dim lRow As Long, i As Long, j As Long, c As Long

    '~~> Change this to the relevant sheet name
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 1 To lRow
            If InStr(1, .Range("A" & i).Value, "/n", vbTextCompare) Then
                MyArray = Split(.Range("A" & i).Value, "/n")
                c = 1
                For j = 0 To UBound(MyArray)
                    .Cells(i, c).Value = MyArray(j)
                    c = c + 1
                Next j
            End If
        Next i
    End With
End Sub

SCREENSHOT (Before)

enter image description here

SCREENSHOT (After)

enter image description here

+1 for VBA)) Will bookmark since it's my everyday need - prepare huge data arrays for testing, and not always it's as simple as above. — Peter L., Feb 07 '13 at 20:22

Split address field in Excel

2 Answers2

Linked