Formula to remove entire words that start with certain characters

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

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.