问题描述
我有一个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函数提取单元格文本的某些部分?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!