如何在Excel中提取地址? [英] How to extract address in excel?
问题描述
我在一个单元格中有地址，我想将它们提取到同一行的不同单元格中.有些单元格有四行地址，有些单元格有三行.我能够使用文本轻松地将列分隔为三个分隔符，而不是四个分隔符.
I have addresses in one cell and I want to extract them in different cells on the same row. Some cells have four lines of address and some have three. I am able to easily split using text to column and various delimiters for the ones with three but not the ones with four.
在此处输入图片描述
在第一个示例中，我有四行，第二条有三行
In the first example I have four lines and second has three
Anchorage Oncology Centre
3801 University Lake Drive
Suite 300-B2
Anchorage, AK 99508 US
我希望将以上内容分成5个单元格.每个单元格一个，用于地址，城市，州，邮政编码和国家/地区
I would like the above as split into 5 cells. One cell each for address, City, State, Zip code and country
Anchorage Oncology Centre
3801 University Lake Drive
Suite 300-B2
Anchorage
AK
99508
US
在下面的第二个示例中
Providence Alaska Medical Center
3200 Providence Drive
Anchorage, AK 99508 US
我想要
Providence Alaska Medical Center
3200 Providence Drive
Anchorage
AK
99508
US
这可以使用公式吗?
到目前为止，我所做的是完整的地址位于单元格A1中，我希望它们位于B1，C1，D1，E1和F1中.我所做的是针对我使用=RIGHT(A2,2)，邮政编码的国家，我使用=MID(A2, LEN(A2)-7, 5)，州名=MID(A2, LEN(A2)-10, 2)的国家.现在，我正在尝试提取这座城市.该城市在逗号之前和换行符(Char(10))之后，并且地址是前2或3行.我不知道该怎么做.
What I have done so far is that the full address is in cell A1 and I want them in B1, C1, D1, E1, and F1. What i have done is for the country I use =RIGHT(A2,2), zip code, i use =MID(A2, LEN(A2)-7, 5), state =MID(A2, LEN(A2)-10, 2). Now I am trying to extract the city. The city is before the comma and after the line break (Char(10)) and Address is first 2 or 3 lines. I don't know how to do that.
每行之间都有一个换行符.
There is a line break between each line.
谢谢
推荐答案
如果使用SUBSTITUTE()，则可以将第n个出现的字符替换为新字符，然后使用FIND()返回该字符.例如，如果您SUBSTITUTE您的CHAR(10)，这是第三次出现，则可以再次找到该字符，该字符将是地址的结尾.
If you use SUBSTITUTE() you can substitute the n'th occurrence of a character with a new character, then use FIND() to return that character. For example, if you SUBSTITUTE your CHAR(10), the third occurrence, you can find that character again that would be the end of the address.
因此，如果您的完整地址在A1中，则可以使用LEFT(A1,FIND("~",SUBSTITUTE(A1,CHAR(10),"~",3)))
So if your FULL address is in A1, then you could extract the address with LEFT(A1,FIND("~",SUBSTITUTE(A1,CHAR(10),"~",3)))
这篇关于如何在Excel中提取地址?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
