CODEWITHSUNDEEP
excelsplitexcel-formula
user4042721
user4042721

Reputation:

Splitting addresses into columns in Excel

I need help splitting addresses into columns in Excel. Addresses in COLUMN A are written like:

601 W Houston St Abbott, TX 76621 United States
13498 US 301 South Riverview, FL 33578 United States

COLUMN B is actually a helper column. It contains only the city names from COLUMN A. My idea was to somehow match COLUMN B with COLUMN A and then all matches move to another column. That would separate City from the Address.
State, Zip and Country I can use "split text to columns" since "comma" is delimiter. But I need help splitting address and the city.
There is a "comma" right after the city name, but some cities has more than one word in city name.

What I need to do is split the addresses like it's highlighted in green in the image below.

What is the best way to do that in Excel? What would be the formula for that?

This is a sample of my spreadsheet

Upvotes: 0

Views: 1454

Answers (3)

user4042721
user4042721

Reputation:

So, it is possible to get it done with the formula. It may not be the best way, but I got what I needed.

  1. I've added a new sheet and named it "cities"
  2. I moved the city list from sheet 1 to COL A in sheet "cities"
  3. In sheet 1, B1 = =INDEX(cities!$A$1:$A$10000;LOOKUP(99^99;MATCH(RIGHT(TRIM(LEFT(SUBSTITUTE(A2;",";REPT(" ";255));255));ROW($A$1:$A$100));cities!$A$1:$A$10000;0)))
  4. Then I've simply use SUBSTITUTE to remove city names from column A in Sheet 1: C1==SUBSTITUTE(A1, B1, "")

And that's it!

Upvotes: 0

eshwar
eshwar

Reputation: 694

Since you already have City in Col B, just replace the city in A

D2 =SUBSTITUTE(A2,C2,"")

Column C Paste special values in Col C

Split Column C using comma.

Then split the Column D using "space". Assuming you have all records in US, you can add the country to all rows if required.

EDIT I missed that the city name in the row does not correspond to the address. To match the city from the Master, you can use this array formula:

C2 =INDEX(B:B,MATCH(1,MATCH(""&$B:$B&"",A2,0),0))

Array formula must be confirmed with Ctrl-Shift-Enter.

However, this will find the first match. If you have cities Foster & Foster City in your master, Foster City wlll never be matched. So, sort the cities in descending order of length.

Once you have the City name matched you can follow the steps I gave earlier. Note that I have adjusted the formula to take into account the city name that has been matched by this new formula.

Upvotes: 0

bobajob
bobajob

Reputation: 1192

We can use a quirk of LOOKUP to get this working.

=LOOKUP(1E+99,FIND(B$2:B$100,A2),B$2:B$100) in D2 will return the city based on searching for matches in column B. Note that this will need the full range of column B specified to be filled.

Then we can put =LEFT(A2,FIND(D2,A2)-2) in C2 to get the first part of the address.

The rest is easy if we can assume that the state, Zip and country are of constant length (if you've got any addressses outside the US then you'll need to alter this):

=LEFT(RIGHT(A2,22),3) in E2

=LEFT(RIGHT(A2,19),5) in F2

=RIGHT(A2,13) in G2

Upvotes: 0

Related Questions