CODEWITHSUNDEEP
excelexcel-formula
codewithawais
codewithawais

Reputation: 579

EXCEL Function to Separate Only Last 3 Values From Address With Comma Delimited

I am using Excel 2013. I have an excel sheet with addresses. I want to put Post Codes, Region, and town in s separate column. The addresses are separated from the comma. Of course, I can use the "Text to Column" function but the issue is that there are also address that have commas within the street address.

For example:

201 Battle Road, st. Leonards-on-sea, East Sussex, TN37 7AJ

&

Inside Pets At Home, unit 3, bridge of don retail park, denmore road, Aberdeen, Aberdeenshire, AB23 8JW

In this example, I am only interested in separating the last 3 values with a comma which is Town name, Region, and Zipcode. But the "Text to Column" function separates all the values which I don't want.

What's the best way to handle this so it only separates the last 3 values?

Thanks for your time.

Upvotes: 1

Views: 377

Answers (1)

JvdV
JvdV

Reputation: 75900

In Excel O365 you could use (using spilling behaviour):

=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A1,", ","</s><s>")&"</s></t>","//s[position()>last()-3]"))

In other Excel versions (from Excel 2013 onwards) you could use:

=INDEX(FILTERXML("<t><s>"&SUBSTITUTE($A1,", ","</s><s>")&"</s></t>","//s[position()>last()-3]"),COLUMN(A1))

And drag two cells to the right.

enter image description here

Upvotes: 2

Related Questions