将地址拆分成Excel中的列 [英] Splitting addresses into columns in Excel

问题描述

我需要帮助将地址分割成Excel中的列。
COLUMN A中的地址写成如下：


601 W Houston St Abbott，TX 76621美国

13498 US 301 South Riverview，FL 33578美国< br>

COLUMN B实际上是一个帮助列。它只包含来自COLUMN A的城市名称。我的想法是以某种方式匹配COLUMN B和COLUMN A，然后所有匹配移动到另一列。这将把City与Address进行分隔。
State，Zip and Country我可以使用split text to columns，因为逗号是分隔符。但我需要帮助分割地址和城市。


城市名后面有一个逗号，但一些城市在城市名称中有不止一个字。


我需要做的是将地址像下面的图片中的绿色突出显示。


在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?

推荐答案

所以，可以用公式来完成它。这可能不是最好的办法，但我得到了我所需要的。

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. 我添加了一张新的表格，并将其命名为cities


2. 在表格1中，列表城市中的COL A列表


3. 在表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. 然后我简单地使用SUBSTITUTE从表1中的列A中删除城市名称：C1 == SUBSTITUTE（A1，B1，）

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, "")

就是这样！

这篇关于将地址拆分成Excel中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！