Format Cells - Hyperlink

[Dileep Chandran]

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

 

[Max]

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))

 

[Max]

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))

 

[Dileep Chandran]

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

 

[Max]

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)))

 

[Dileep Chandran]

Max,

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

-Dileep

 

[Max]

Pleasure` Dileep.
Thanks for the feedback ..