删除以某些字符开头的整个单词的公式 [英] Formula to remove entire words that start with certain characters
问题描述
我想要一个公式,搜索所有出现的 http
的字符串,并删除整个链接。例如:
这是有史以来最好的故事http://www.usatoday.com确保阅读两次。 http://www.usatoday.com/image.jpg
将成为:
这是最好的故事,确保读取两次。
从我所看到的,应该这样做:
= TRIM(LEFT(A1,FIND(http,A1)-1))& RIGHT(A1,LEN(A1)-FIND( A1,FIND(http,A1))+ 1)
但是我仍然得到 #VALUE!
。
我希望能够让代码在串。另外,如果没有找到URL,我只想重新打印原始字符串。
任何想法?
请尝试:
= TRIM(REPLACE(A1,FIND(http://,A1)),IFERROR(FIND(,A1,FIND http://,A1)),LEN(A1)+9)-FIND(http://,A1)+1,))
pre>
如果您希望删除URLS,不仅仅是在句子结尾。
IMO从@Siddharth Rout提供的以下编辑提供了比上述更好的解决方案。
非VBA /非公式方法
- 新闻 CTRL + H 带来
查找和替换
对话框。
- 在
中查找什么
,键入http:// *,不带引号。注意*
- 将
替换为
为空。
- 点击
替换全部
- 现在在
code>,键入http:// *,不带引号。注意在
*
之后没有空格
- 保持
替换为
li>
- 点击
替换全部
而你完成了。
I want a formula that searches a string for all occurrences of
http
and removes that entire link. For instance:This is the best story ever http://www.usatoday.com make sure to read it twice. http://www.usatoday.com/image.jpg
would become:
This is the best story ever make sure to read it twice.
From what I've read, this should do it:
=TRIM(LEFT(A1,FIND("http",A1)-1))&RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND("http",A1))+1)
but I'm still getting
#VALUE!
.I'd like to be able to have the code find the URL at any point in the string. Also, if no URL is found, I'd just like the original string reprinted.
Any ideas?
解决方案Please try:
=TRIM(REPLACE(A1,FIND("http://",A1),IFERROR(FIND(" ",A1,FIND("http://",A1)),LEN(A1)+9)-FIND("http://",A1)+1,""))
if you're looking to remove URLS not just at end of sentence.
IMO the following edit from @Siddharth Rout provides a better solution than the above.
Non VBA / Non Formula Method
- Press CTRL + H to bring the
Find And Replace
Dialog Box.- In
Find What
, type "http://* " without the quotes. Notice there is a space after*
- Keep the
Replace With
empty.- Click
Replace All
- Now in
Find What
, type "http://*" without the quotes. Notice there no space after*
- Keep the
Replace With
empty- Click
Replace All
And you are done.
这篇关于删除以某些字符开头的整个单词的公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!