Format Cells - Hyperlink

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dileep Chandran - 30 Oct 2006 06:47 GMT

Hi,

I am just trying to format a range of cells. What I need is, If we
enter a web address say, http://yahoo.com, the cell should
automatically convert it as http://www.yahoo.com.

Is it possible?. Any help is greatly appreciated.

Thanks

-Dileep Chandran

Reply to this Message

Max - 30 Oct 2006 07:11 GMT

One guess ... with source entries in A1 down,
perhaps in B1, copied down:
="http://www."&MID(A1,SEARCH("/",A1)+2,99)
(above returns as text)

Or, as clickable hyperlinks, in B1 down
=HYPERLINK(A1,"http://www."&MID(A1,SEARCH("/",A1)+2,99))

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hi,
>
[quoted text clipped - 7 lines]
>
> -Dileep Chandran

Reply to this Message

Max - 30 Oct 2006 07:18 GMT

Correction to 2nd one, sorry ...

> Or, as clickable hyperlinks, in B1 down
> =HYPERLINK(A1,"http://www."&MID(A1,SEARCH("/",A1)+2,99))

Try instead in B1:
=HYPERLINK("http://www."&MID(A1,SEARCH("/",A1)+2,99),"http://www."&MID(A1,SEARCH("/",A1)+2,99))

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Dileep Chandran - 30 Oct 2006 07:35 GMT

Max,

This formula is working fine. Thanks !
But if the cell contains an address in the desired format (ie. starting
with "http://www."), the formula should not change it. Is there a way
to do it?

Is my question clear?

Dileep

Reply to this Message

Max - 30 Oct 2006 07:44 GMT

Try this in B1:
=IF(ISNUMBER(SEARCH("www",A1)),HYPERLINK(A1,A1),HYPERLINK("http://www."&MID(A1,SEARCH("/",A1)+2,99),"http://www."&MID(A1,SEARCH("/",A1)+2,99)))

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Max,
>
[quoted text clipped - 6 lines]
>
> Dileep

Reply to this Message

Dileep Chandran - 30 Oct 2006 09:26 GMT

Max,

Thank you very much for the help. This is what exactly I was looking
for.

-Dileep

Reply to this Message

Max - 30 Oct 2006 10:03 GMT

Pleasure` Dileep.
Thanks for the feedback ..

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Max,
>
> Thank you very much for the help. This is what exactly I was looking
> for.
>
> -Dileep

Reply to this Message