Formula to remove specific text from a cell

[Dio]

I have a database that returns me urls with text at the beginning that
is always the same. for instance: 404:http://myurl.com

I want a formula that will automatically put the hyperlink in a cell
by itself (in this case, http://myurl.com). Extra bonus points if that
cell can be clickable!

Thanks in advance. Sorry to throw out such an easy one, but I've
searched for a while and just can't figure it out.

 

[Alan]

=SUBSTITUTE(A1,"404:","")

Right click the cell, Copy, right click again, Paste Special, check
'Values', OK
Select the cell and press F2,

Regards,
Alan.

 

[Dio]

Alan, thank you for the answer!

I knew the paste special trick, but I was hoping there was a way to
automate the hyperlink step. Lazy, I know, but it's going to add up to
a lot of cells in the longrun.

Thanks again for the help,
Dio.

 

[Niek Otten]

=HYPERLINK(SUBSTITUTE(A1,"404:",""))