使用MID，LEN和FIND函数提取单元格文本的某些部分? [英] Use MID, LEN, and FIND functions to extract certain portions of a cells text?

问题描述

我有一个Excel作业，我被困在最后一部分.我被要求使用MID，LEN和FIND来提取单元格中的特定字符串.我对每个人的工作方式都有很好的了解；将所有这三个方法结合起来并使其正常工作是我遇到的问题.

I have an Excel assignment and I'm stuck on the final portion. I'm asked to use MID, LEN and FIND in order to extract specific strings within cells. I have good understanding of how each one works individually; combining all three and getting them to work is the problem I'm having.

我需要将城市与其他地址分开，然后将其显示在单独的单元格中.我需要从B列中获取数据，仅在G列中显示城市.这就是我的工作表和公式:

I need to separate the city from the rest of the address, which will then be displayed in a separate cell. I need to take the data from column B and only display the city in column G. This is what my worksheet and formula looks like:

公式: = MID(B3，FIND(，"，B3)+1,15)

工作表

带有公式的工作表

我自己有这个城市.但是，如您所见，仍然有一个空格，状态仍在显示.我不确定如何将LEN与MID和FIND结合使用以摆脱状态标识符.

I have the city somewhat by itself. However, as you can see, there is still a space and the state is still showing. I'm not sure how to use LEN with MID and FIND in order to get rid of the state identifier.

推荐答案

由于您知道如何查找城市，并且想在其后剪切其余文本，因此可以将Len与Find结合使用:

Since you know how to find the city, and want to cut the rest of the text after it, you can use Len combined with Find:

=MID(A1,FIND(",",B3)+1,LEN(B3)-FIND(",",B3)-3)

Len(B3)-Find(...)-3将是要返回的字符数.在您的第一个示例中，该值为24-15-3 = 6.因此它将返回空格，然后是米兰"

Len(B3)-Find(...)-3 will be the Number of Characters to return, after finding the ,. This, in your first example, will be 24-15-3=6. So it'll return the space, then "Milan"

然后，将Trim()包裹起来以除去所有前导和/或尾随空格.

Then, just wrap Trim() around all that to remove any leading and/or trailing space.

只是为了好玩，如果您拥有超过NM(或两个字母的缩写)的内容，则可以使用以下方法:

Just for fun, in case you ever have more than NM (or a two letter abbreviation), you can use this:

=MID(SUBSTITUTE(B3," ",";",4),SEARCH(",",SUBSTITUTE(B3," ",";",4))+1,SEARCH(";",SUBSTITUTE(B3," ",";",4))-1-SEARCH(",",SUBSTITUTE(B3," ",";",4)))

因此，如果您有1880 Atkins Rd, Milan New Mexico，它仍然会只返回Milan.

So, if you have 1880 Atkins Rd, Milan New Mexico, it'll still just return Milan.

这篇关于使用MID，LEN和FIND函数提取单元格文本的某些部分?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！