Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format Cells - Hyperlink
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format Cells - Hyperlink
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 Singapore http://savefile.com/projects/236895 xdemechanik --- "Dileep Chandran" wrote in message ps.com... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format Cells - Hyperlink
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)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format Cells - Hyperlink
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format Cells - Hyperlink
Try this in B1:
=IF(ISNUMBER(SEARCH("www",A1)),HYPERLINK(A1,A1),HY PERLINK("http://www."&MID(A1,SEARCH("/",A1)+2,99),"http://www."&MID(A1,SEARCH("/",A1)+2,99))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dileep Chandran" wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format Cells - Hyperlink
Max,
Thank you very much for the help. This is what exactly I was looking for. -Dileep |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format Cells - Hyperlink
Pleasure` Dileep.
Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dileep Chandran" wrote in message oups.com... Max, Thank you very much for the help. This is what exactly I was looking for. -Dileep |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format Cells - Accounting - Discrepancy? | Excel Discussion (Misc queries) | |||
Why will my cells not format? | New Users to Excel | |||
how to format only specific characters or numbers within each cellwithin a range of cells | Excel Discussion (Misc queries) | |||
Number format exactly the same, displays differently in some cells | Excel Discussion (Misc queries) | |||
format cells dialog box does not come up | Excel Discussion (Misc queries) |