CODEWITHSUNDEEP
excelvbaexcel-formulaspreadsheet
Iman Ghavamabadi
Iman Ghavamabadi

Reputation: 47

MS Excel - Separate texts by delimiter into specific columns

In MS Excel, I need to separate text by comma, extract three things from right and put them in three different columns i.e. city, State and Country.

Problem: In the original text which is the full address, I sometimes get only the country or only the state. Also, the length of the full address is varied.

Here's a screenshot of what I intend to do

and Here's the actual spreadsheet.

Upvotes: 0

Views: 165

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

If you have Excel 2013+ with the FILTERXML function, you can use:

=IFERROR(FILTERXML("<t><s>" & SUBSTITUTE($A1,",","</s><s>") & "</s></t>","//s[last()-3+" & COLUMNS($A:A) & "]"),"")

Fill across and down.

  • Create an XML with each comma-separated string being a node
  • Extract the last 3 nodes, returning a "" if it is not existent.

enter image description here

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

First enter the following three user defined functions in a standard module:

Public Function LastWord(s As String) As String
    Dim arr
    s = ", , , , , " & s
    arr = Split(s, ", ")
    LastWord = arr(UBound(arr))
End Function

Public Function NLastWord(s As String) As String
    Dim arr
    s = ", , , , , " & s
    arr = Split(s, ", ")
    NLastWord = arr(UBound(arr) - 1)
End Function

Public Function NNLastWord(s As String) As String
    Dim arr
    s = ", , , , , " & s
    arr = Split(s, ", ")
    NNLastWord = arr(UBound(arr) - 2)
End Function

then in E1 enter:

=lastword(A1)

and copy downward. In D1 enter:

=NLastWord(A1)

and copy downwards. In C1 enter:

=NNLastWord(A1)

and copy downwards:

enter image description here

Upvotes: 0

bosco_yip
bosco_yip

Reputation: 3802

Assume data housed in A1:A6 as per screenshot

In C1, formula copied across and down :

=IFERROR(TRIM(MID(SUBSTITUTE(","&$A1,",",REPT(" ",399)),(LEN($A1)-LEN(SUBSTITUTE(($A1),",",""))+COLUMN(A1)-2)*399,399)),"")

Upvotes: 3

Related Questions