How to edit in every cell with a common rule
 

I am a novice user, and would really appreciate any help.
If I want to prepend all non-empty cells in my work sheet with some
strings. How should I do it?
For example, all my cells are having data as web site names. I would like to
prepend "http://" to make the same clickable.

Alternatively, how should I change the property of all non-blank cells as to
the hyper link to connect to the same site as given as cell value.

Please help.
Thanks a lot,

Do you really mean all the cells in your worksheet?

And if you do, are all the cells in one column?

If they are, I'd use a helper column and put a formula like:

=hyperlink("http://" & a1)

and copy down that column

Then hide (not delete) the original column.



Kishore wrote:

I am a novice user, and would really appreciate any help.
If I want to prepend all non-empty cells in my work sheet with some
strings. How should I do it?
For example, all my cells are having data as web site names. I would like to
prepend "http://" to make the same clickable.

Alternatively, how should I change the property of all non-blank cells as to
the hyper link to connect to the same site as given as cell value.

Please help.
Thanks a lot,

--

Dave Peterson

And as a macro that builds this formula in the original cell:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select some cells with values!"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Formula = "=hyperlink(""http://" & myCell.Value & """)"
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Dave Peterson wrote:

Do you really mean all the cells in your worksheet?

And if you do, are all the cells in one column?

If they are, I'd use a helper column and put a formula like:

=hyperlink("http://" & a1)

and copy down that column

Then hide (not delete) the original column.

Kishore wrote:

I am a novice user, and would really appreciate any help.
If I want to prepend all non-empty cells in my work sheet with some
strings. How should I do it?
For example, all my cells are having data as web site names. I would like to
prepend "http://" to make the same clickable.

Alternatively, how should I change the property of all non-blank cells as to
the hyper link to connect to the same site as given as cell value.

Please help.
Thanks a lot,

--

Dave Peterson

--

Dave Peterson