I have a list of locations in the format A2: [ City State ], where in the City or State can be made up of more than two words. All of this is in a single cell. I have the total data in thousands. Now, I also have a list of State with me. What I need to do is remove the State from A2 and just have the remaining string there. Thus leaving me with only City. I need help with this as the data is in millions and I have a list of around 30K cities.
我有一个格式为A2: [City State]的地点列表，其中城市或州可以由两个以上的词组成。所有这些都在一个单元格中。我有成千上万的数据。现在，我还有一个状态列表。我需要做的是从A2中移除状态，只保留剩下的字符串。这样我就只剩下城市了。我需要帮助，因为数据数以百万计，我有大约3万个城市的名单。
1 个解决方案
#1
1
You can create 2 cells next to the cell that has the data one for City and one for State. enter the formulas and autofill down.
您可以在单元格旁边创建两个单元格，其中一个用于城市，另一个用于州。输入公式并自动填充。
Formula to extract City
公式提取城市
=left(A1,find(" ",A1,1)-1)
Formula to extract State
公式提取状态
=right(A1,len(A1)-find(" ",A1,1))
These formulas take the text to the left of the first space and the text to the right of the first space. You will have a problem if the city has a space e.g. "New York" but it is very difficult to work around that.
这些公式将文本移到第一个空间的左边，将文本移到第一个空间的右边。如果这个城市有空地，你就有麻烦了。“纽约”，但要围绕它工作是非常困难的。
The other way to do this is to loop through all the cells using VBA code and run the same code on the cell and delete the State from that cell and place the State in a different cell. it's a more complicated option.
另一种方法是使用VBA代码遍历所有单元，并在单元上运行相同的代码，并从该单元中删除状态，并将状态放在另一个单元中。这是一个更复杂的选择。
To solve the cities with 2 words problem
用两个词来解决城市问题
I'm assuming that the Sates are US States and so I've created a string of all the US states that contain 2 words
我假设Sates是美国，所以我创建了一个包含两个单词的所有美国州的字符串。
[New Hampshire][New Jersey][New Mexico][New York][North Carolina][North Dakota][Rhode Island][South Carolina][South Dakota][West Virginia]
[新罕布什尔][新墨西哥州][纽约州][北卡罗来纳][北达科他州][罗德岛][南卡罗来纳][南达科他州][西弗吉尼亚州]
If you paste these states into cell B1 then you can use the formula below in cell B3 to check the contents of A3 and extract the city.
如果你将这些状态粘贴到B1单元中，你可以使用下面的公式在B3单元中检查A3的内容并提取城市。
=IF(ISNUMBER(FIND("[" & RIGHT(A3,LEN(A3)-FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-1-LEN(SUBSTITUTE(A3," ",""))))) & "]",$B$1,1)),LEFT(A3,FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-1-LEN(SUBSTITUTE(A3," ",""))))-1),LEFT(A3,FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))))-1))
Then paste the following formula into cell C3 to check the contents of A3 and extract the State.
然后将以下公式粘贴到cell C3中，检查A3的内容，提取状态。
=IF(ISNUMBER(FIND("[" & RIGHT(F9,LEN(F9)-FIND("☃",SUBSTITUTE(F9," ","☃",LEN(F9)-1-LEN(SUBSTITUTE(F9," ",""))))) & "]",$B$1,1)),RIGHT(F9,LEN(F9)-FIND("☃",SUBSTITUTE(F9," ","☃",LEN(F9)-1-LEN(SUBSTITUTE(F9," ",""))))),RIGHT(F9,LEN(F9)-FIND("☃",SUBSTITUTE(F9," ","☃",LEN(F9)-LEN(SUBSTITUTE(F9," ",""))))))
Formulas to deal with States with 3 words
用三个词表示状态的公式
The formula is getting really long but all it's doing is checking for a match with a split at 3 spaces from the end and then 2 spaces and if a state is not found splitting at 1 space.
这个公式变得很长，但它所做的只是检查一个匹配，在3个空格处，然后是2个空格，如果一个状态没有在1个空间分裂。
Extract City
提取城市
=IF(ISNUMBER(FIND("["&RIGHT(A3,LEN(A3)-FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-2-LEN(SUBSTITUTE(A3," ","")))))&"]",$B$1,1)),LEFT(A3,FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-2-LEN(SUBSTITUTE(A3," ",""))))-1),IF(ISNUMBER(FIND("["&RIGHT(A3,LEN(A3)-FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-1-LEN(SUBSTITUTE(A3," ","")))))&"]",$B$1,1)),LEFT(A3,FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-1-LEN(SUBSTITUTE(A3," ",""))))-1),LEFT(A3,FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))))-1)))
Extract State
提取状态
=IF(ISNUMBER(FIND("["&RIGHT(A3,LEN(A3)-FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-2-LEN(SUBSTITUTE(A3," ","")))))&"]",$B$1,1)),RIGHT(A3,LEN(A3)-FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-2-LEN(SUBSTITUTE(A3," ",""))))),IF(ISNUMBER(FIND("["&RIGHT(A3,LEN(A3)-FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-1-LEN(SUBSTITUTE(A3," ","")))))&"]",$B$1,1)),RIGHT(A3,LEN(A3)-FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-1-LEN(SUBSTITUTE(A3," ",""))))),RIGHT(A3,LEN(A3)-FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-LEN(SUBSTITUTE(A3," ","")))))))
How it works The formula looks for the second to last space by taking one away from the last space (thanks to user m4573r for the last space formula). It then takes everything right of the last space, adds "[]" brackets and checks this against the State list text in B1. If it exists then it uses the second last space at the point to cut the text in half and return either the City from the left or the State on the right.
它是如何工作的公式通过从最后一个空间拿走一个空间来寻找第二个到最后一个空间(感谢用户m4573r为最后一个空间公式)。然后，它将最后一个空格中的所有内容都取对，并添加“[]”括号，并将其与B1中的状态列表文本进行检查。如果它存在，那么它将使用第二个最后的空间，将文本分割成两半，从左边或右边返回城市。
#1
1
You can create 2 cells next to the cell that has the data one for City and one for State. enter the formulas and autofill down.
您可以在单元格旁边创建两个单元格，其中一个用于城市，另一个用于州。输入公式并自动填充。
Formula to extract City
公式提取城市
=left(A1,find(" ",A1,1)-1)
Formula to extract State
公式提取状态
=right(A1,len(A1)-find(" ",A1,1))
These formulas take the text to the left of the first space and the text to the right of the first space. You will have a problem if the city has a space e.g. "New York" but it is very difficult to work around that.
这些公式将文本移到第一个空间的左边，将文本移到第一个空间的右边。如果这个城市有空地，你就有麻烦了。“纽约”，但要围绕它工作是非常困难的。
The other way to do this is to loop through all the cells using VBA code and run the same code on the cell and delete the State from that cell and place the State in a different cell. it's a more complicated option.
另一种方法是使用VBA代码遍历所有单元，并在单元上运行相同的代码，并从该单元中删除状态，并将状态放在另一个单元中。这是一个更复杂的选择。
To solve the cities with 2 words problem
用两个词来解决城市问题
I'm assuming that the Sates are US States and so I've created a string of all the US states that contain 2 words
我假设Sates是美国，所以我创建了一个包含两个单词的所有美国州的字符串。
[New Hampshire][New Jersey][New Mexico][New York][North Carolina][North Dakota][Rhode Island][South Carolina][South Dakota][West Virginia]
[新罕布什尔][新墨西哥州][纽约州][北卡罗来纳][北达科他州][罗德岛][南卡罗来纳][南达科他州][西弗吉尼亚州]
If you paste these states into cell B1 then you can use the formula below in cell B3 to check the contents of A3 and extract the city.
如果你将这些状态粘贴到B1单元中，你可以使用下面的公式在B3单元中检查A3的内容并提取城市。
=IF(ISNUMBER(FIND("[" & RIGHT(A3,LEN(A3)-FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-1-LEN(SUBSTITUTE(A3," ",""))))) & "]",$B$1,1)),LEFT(A3,FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-1-LEN(SUBSTITUTE(A3," ",""))))-1),LEFT(A3,FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))))-1))
Then paste the following formula into cell C3 to check the contents of A3 and extract the State.
然后将以下公式粘贴到cell C3中，检查A3的内容，提取状态。
=IF(ISNUMBER(FIND("[" & RIGHT(F9,LEN(F9)-FIND("☃",SUBSTITUTE(F9," ","☃",LEN(F9)-1-LEN(SUBSTITUTE(F9," ",""))))) & "]",$B$1,1)),RIGHT(F9,LEN(F9)-FIND("☃",SUBSTITUTE(F9," ","☃",LEN(F9)-1-LEN(SUBSTITUTE(F9," ",""))))),RIGHT(F9,LEN(F9)-FIND("☃",SUBSTITUTE(F9," ","☃",LEN(F9)-LEN(SUBSTITUTE(F9," ",""))))))
Formulas to deal with States with 3 words
用三个词表示状态的公式
The formula is getting really long but all it's doing is checking for a match with a split at 3 spaces from the end and then 2 spaces and if a state is not found splitting at 1 space.
这个公式变得很长，但它所做的只是检查一个匹配，在3个空格处，然后是2个空格，如果一个状态没有在1个空间分裂。
Extract City
提取城市
=IF(ISNUMBER(FIND("["&RIGHT(A3,LEN(A3)-FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-2-LEN(SUBSTITUTE(A3," ","")))))&"]",$B$1,1)),LEFT(A3,FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-2-LEN(SUBSTITUTE(A3," ",""))))-1),IF(ISNUMBER(FIND("["&RIGHT(A3,LEN(A3)-FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-1-LEN(SUBSTITUTE(A3," ","")))))&"]",$B$1,1)),LEFT(A3,FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-1-LEN(SUBSTITUTE(A3," ",""))))-1),LEFT(A3,FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))))-1)))
Extract State
提取状态
=IF(ISNUMBER(FIND("["&RIGHT(A3,LEN(A3)-FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-2-LEN(SUBSTITUTE(A3," ","")))))&"]",$B$1,1)),RIGHT(A3,LEN(A3)-FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-2-LEN(SUBSTITUTE(A3," ",""))))),IF(ISNUMBER(FIND("["&RIGHT(A3,LEN(A3)-FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-1-LEN(SUBSTITUTE(A3," ","")))))&"]",$B$1,1)),RIGHT(A3,LEN(A3)-FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-1-LEN(SUBSTITUTE(A3," ",""))))),RIGHT(A3,LEN(A3)-FIND("☃",SUBSTITUTE(A3," ","☃",LEN(A3)-LEN(SUBSTITUTE(A3," ","")))))))
How it works The formula looks for the second to last space by taking one away from the last space (thanks to user m4573r for the last space formula). It then takes everything right of the last space, adds "[]" brackets and checks this against the State list text in B1. If it exists then it uses the second last space at the point to cut the text in half and return either the City from the left or the State on the right.
它是如何工作的公式通过从最后一个空间拿走一个空间来寻找第二个到最后一个空间(感谢用户m4573r为最后一个空间公式)。然后，它将最后一个空格中的所有内容都取对，并添加“[]”括号，并将其与B1中的状态列表文本进行检查。如果它存在，那么它将使用第二个最后的空间，将文本分割成两半，从左边或右边返回城市。