CODEWITHSUNDEEP
excelexcel-2007excel-formulavba
user2012677
user2012677

Reputation: 5745

Split address field in Excel

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?

Upvotes: 1

Views: 2056

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149315

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

Upvotes: 3

Peter L.
Peter L.

Reputation: 7304

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.

Upvotes: 5

Related Questions