Excel从字符串的任何部分提取一个单词 [英] Excel extract one word from the any part of the string

问题描述

我有一个包含地址的字符串，该部分用逗号分隔.

位于巴斯(Bath)CB1 3QQ号的洛克斯布鲁克路(巴斯温泉大学)洛克布鲁克校园外(位于D19单元格中)

我想从该字符串中提取城市名称，即"Bath".城市名称始终要放在邮政编码之前.

我已经分两步进行了管理.首先，我提取了最后一个逗号之前的所有内容，例如:

用于修剪函数的Excel IF语句

  = MID(D19,1，FIND("@"，SUBSTITUTE(D19，，"，"@"，LEN(D19)-LEN(SUBSTITUTE(D19，)，，")))))) 

这给了我

巴斯巴斯夫洛克布鲁克路洛克斯布鲁克校区(巴斯温泉大学)外，

接下来，我必须使用以下公式来提取新字符串中的最后一个单词:

  = TRIM(RIGHT(SUBSTITUTE(AA5，，" ,, REPT(，" ,, 100))，100)) 

这几乎给了我最终结果:

洗澡

但是，我只想通过1次操作即可完成.

有可能吗?

我在这里找到了一些公式，该公式必须从字符串中提取任何单词(第n个单词)，但遗憾的是，在我的情况下它不起作用.

https://exceljet.net/formula/extract-nth-word-from-text-string

  = TRIM(MID(SUBSTITUTE(D19，，"，REPT(",, LEN(D19)))，(N-1)* LEN(D19)+1，LEN(D19))) 

它最终给了我 #NAME?.

此外，令我担忧的是，当地址字符串稍有不同时，它不起作用，其中包含更多的元素，例如街区名称，街道名称，城市，邮政编码或仅街道名称，城市和邮政编码.

有什么方法可以从字符串中仅提取最后一个逗号之前(最终极逗号之前)的一部分吗?

解决方案

您可以尝试:

  = FILTERXML("t> s">"SUBSTITUTE(D19，"，，"/s"));</s>/t""，"//s [position()= last()-1]") 

xpath表达式//s [position()= last()-1] 告诉该函数返回位置索引倒数第二个s节点.

有关 FILTERXML()的更多信息以及使用某些xpath返回感兴趣的子字符串的信息，您可能会发现Excel IF statement for trim function

  =MID(D19,1,FIND("@",SUBSTITUTE(D19,",","@",LEN(D19)-LEN(SUBSTITUTE(D19,",","")))))

which gave me:

Outside Locksbrook Campus (Bath Spa University) Locksbrook Road, Bath,

and next, I had to extract the last word in my new string by using this formula:

  =TRIM(RIGHT(SUBSTITUTE(AA5," ",REPT(" ",100)),100))

which gave me pretty much the final result:

Bath,

However, I would like to make it done with 1 operation only.

Is it possible at all?

I found some formula here, which should have to extract any word (the nth word) from the string, but regrettably, it doesn't work in my case.

https://exceljet.net/formula/extract-nth-word-from-text-string

   =TRIM(MID(SUBSTITUTE(D19," ",REPT(" ",LEN(D19))), (N-1)*LEN(D19)+1, LEN(D19)))

it gives me #NAME? finally.

Moreover, I have misgivings, that it won't work, when the address string will be slightly different, containing more elements like block name, street name, city, postcode or only street name, city, and postcode.

Is there any way to extract only one section from the string before the last comma (after the pre ultimate comma)?

解决方案

You may try:

=FILTERXML("<t><s>"&SUBSTITUTE(D19,", ","</s><s>")&"</s></t>","//s[position()=last()-1]")

The xpath expression //s[position()=last()-1] tells the function to return the s-node that is second to last by position index.

For more information on FILTERXML() and the use of some xpath to return substrings of interest, you may find this interesting.

这篇关于Excel从字符串的任何部分提取一个单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！