Formula to remove entire words that start with certain characters

Question

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?

Solution 1

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

1. Press CTRL + H to bring the Find And Replace Dialog Box.
2. In Find What, type "http://* " without the quotes. Notice there is a space after *
3. Keep the Replace With empty.
4. Click Replace All
5. Now in Find What, type "http://*" without the quotes. Notice there no space after *
6. Keep the Replace With empty
7. Click Replace All

And you are done.

Solution 2

You can create your own formula to use regexp expressions in Excel.

1. in Excel open Visual Basic Editor
2. Go to Tools > References...
3. Check Microsoft VBScript Regular Expressions 1.0
4. and Microsoft VBScript Regular Expressions 5.5
5. Then right-click your VBAProject (Book1.xlsx) on the left pane
6. Insert > Module

7. Paste this code

   Public Function RgxReplace(aregexp As String, _
   astring As Range, _
   areplace As String) As String
   Dim re As RegExp
   Set re = New RegExp
   re.Pattern = aregexp
   RgxReplace = re.Replace(astring, areplace)
   End Function
   

8. And save

Now yoг have a new formula in your list

which you can use to regexp replace the string using patterns. In your case it will be

(special thanks to vdasus)

Solution 3

try this:

=TRIM(MID(A1,1,SEARCH("http",A1)-1))

No way to test it so i leave it to you.