使用Excel公式拆分地址

If a cell contains a full address, what formulas would you use to show the street address, city, state and zip code in separate cells? That was the challenge that I gave to my weekly newsletter readers. A few rows of the sample data are show below, and you can download the "Split Address with Excel Formulas" sample file, to see all the data.

如果一个单元格包含完整的地址，您将使用什么公式在单独的单元格中显示街道地址，城市，州和邮政编码？ 那就是我给每周通讯的读者带来的挑战。 下面显示了几行示例数据，您可以下载“使用Excel公式拆分地址”示例文件，以查看所有数据。

()

样本数据 (The Sample Data)

For this challenge, I kept the data pretty clean – you might not be so lucky in real life!. Here are the first few rows of full addresses, with some key elements highlighted.

为了应对这一挑战，我保持了数据的干净整洁-您在现实生活中可能并不那么幸运！ 这是完整地址的前几行，并突出显示了一些关键要素。

在哪里分割地址 (Where to Split the Address)

As you can see in the screen shot above, there are consistent characters that will help you split the data:

如您在上面的屏幕截图中所见，有一致的字符可以帮助您分割数据：

• There is a hyphen after the street address, with a space character before and after it
  街道地址后面有一个连字符，前面和后面都有一个空格
• There is a comma at the end of the city name, then a space character (Some of the street addresses also contain a comma)
  城市名称的末尾有一个逗号，然后是一个空格字符(某些街道地址也包含一个逗号)
• The state is a 2-letter code, in upper case, with a space character before and after it
  状态是一个2字母的代码，用大写字母表示，其前后都有一个空格
• The zip code is 9 or 5 numbers, at the end of the address (Note: a zip code could start with a zero)
  邮政编码是9或5个数字，位于地址的末尾(注意：邮政编码可以以零开头)

快速填充到拆分地址 (Flash Fill to Split Address)

Some people said they would just use Flash Fill to split the addresses. That's a quick and easy option if the full addresses won't be edited later.

有人说，他们只会使用Flash Fill拆分地址 。 如果以后不编辑完整地址，那将是一个快速简便的选择。

Flash Fill is available in Excel 2013 and later, and it helps you extract information, based on patterns in your data. You can use it to put names in reverse order, split dates and times, and many other data tasks.

Flash Fill在Excel 2013和更高版本中可用，它可以帮助您基于数据中的模式提取信息。 您可以使用它以相反的顺序放置名称 ， 分割日期和时间以及许多其他数据任务。

This animated gif shows how to fill in the first two rows, then use the Flash Fill command in each column, to get the data.

此动画gif显示了如何填充前两行，然后在每一列中使用Flash Fill命令来获取数据。

拆分地址的公式 (Formulas to Split Address)

If the full addresses might be edited later, Flash Fill entries won't be updated automatically. Instead, you can use formulas to extract the separate parts from the full address.

如果以后可以编辑完整地址，则Flash Fill条目不会自动更新。 相反，您可以使用公式从完整地址中提取单独的部分。

I was amazed to see how many different solutions that people sent in. No two people used all the same formulas, and there were hardly any duplicates within each column.

我很惊讶地看到人们输入了多少种不同的解决方案。没有两个人使用所有相同的公式，并且每列中几乎没有重复项。

However, there were a few functions that were key to solving the challenge:

但是，有一些功能是解决挑战的关键：

• SEARCH or FIND, to locate a specific character in the full address
  搜索或查找，以在完整地址中查找特定字符
• LEFT, MID and RIGHT to return characters from a starting position
  LEFT，MID和RIGHT从起始位置返回字符

公式范例 (Example Formulas)

Here are example formulas -- one for each of the fields. You can download the sample workbook, to see all the other solutions:

以下是示例公式-每个字段一个。 您可以下载示例工作簿，以查看所有其他解决方案：

• Street Address: =LEFT([@FullAddress], FIND("-",[@FullAddress])-2)

  街道地址： = LEFT([@ FullAddress]，FIND(“-”，[@ FullAddress])-2)

• City: =MID([@FullAddress],LEN([@StreetAddress])+4, FIND(",",SUBSTITUTE([@FullAddress],[@StreetAddress],""))-4)

  城市： = MID([@ FullAddress]，LEN([@ StreetAddress])+ 4，FIND(“，”，SUBSTITUTE([@ FullAddress]，[@ StreetAddress]，“”))-4)

• State: =MID([@FullAddress],FIND([@City],[@FullAddress])+LEN([@City])+2,2)

  状态： = MID([@ FullAddress]，FIND([@ City]，[@ FullAddress])+ LEN([@ City])+ 2,2)

• Zip Code: =MID([@FullAddress],(FIND([@State],[@FullAddress]))+3,9)

  邮政编码： = MID([@ FullAddress]，(FIND([@ State]，[@ FullAddress]))+ 3,9)

For details on how these formulas work, go to the Split Address Formulas page on my Contextures website.

有关这些公式如何工作的详细信息，请转到Contextures网站上的“拆分地址公式”页面 。

获取样本工作簿 (Get the Sample Workbook)

To see the full address data, and all the solutions, download the sample workbook from the Split Address Formulas page on my Contextures website (scroll to the Download section).

若要查看完整的地址数据和所有解决方案，请从Contextures网站上的“拆分地址公式”页面下载示例工作簿(滚动至“下载”部分)。

The zipped file is in xlsx format, and does not contain any macros.

压缩文件为xlsx格式，不包含任何宏。

翻译自: https://contexturesblog.com/archives/2017/06/15/split-address-with-excel-formulas/