how do i add the same text after current text in multiple cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have about 500 cells in 1 column where i need to add the same email address
domain for example - cell A2 reads Sue, cell A3 reads Bob, cell A4 reads Tom
(on down for about 500 cells). I need to simply add @thislocation.com to each
of them - so I wants A2 to read (e-mail address removed), A3 to read
(e-mail address removed), A4 to read (e-mail address removed) - help? I also need
each of these cells to be active email links. (which u can't get if you add
the @thislocation.com to each cell - you have to add it in the formula area
at the top).

Thanks
 
One way

=HYPERLINK("mailto:"&A2&"@thislocation.com",A2&"@thislocation.com")

copy down

Regards,

Peo Sjoblom
 
Sue,

It could be done with a macro. Here's another way, though it'll take a
little while.

In an adjacent cell, put =A2 & "@thislocation.com". Copy down with Fill
Handle. You should see (e-mail address removed). Now select all this stuff,
Copy. Now select A2, Edit - Paste special - Values. At this point, you can
trash the formula column. Now it gets a little tedious. Start in A2, and
repeatedly press F2, Enter, working your way down. Each should now be in
blue, and be a workable email hyperlink.

If you're willing to put in a macro, we'll write you one. Someone has
probably done that by now anyway.
 
Sue

Assuming names are in column A.

In B1 enter =A1 & "@thislocation.com"

Double-click on fill handle of B1 to copy down.

Copy column B and in place Paste Special>Values>OK>Esc.

To change all to Hyperlinks run this macro.

Sub MakeHyperlinks()
'David McRitchie
Dim Cell As Range
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
With Worksheets(1)
.Hyperlinks.Add Anchor:=Cell, _
Address:=Cell.Value, _
ScreenTip:=Cell.Value, _
TextToDisplay:=Cell.Value
End With
Next Cell
End Sub


OR a combo macro to add the text then change to hyperlink.

Sub Add_Text_Right()
Dim Cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter your Text")
For Each Cell In thisrng
Cell.Value = Cell.Value & moretext
Cell.Hyperlinks.Add Anchor:=Cell, _
Address:=Cell.Value, _
ScreenTip:=Cell.Value, _
TextToDisplay:=Cell.Value
Next
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub


Gord Dibben Excel MVP
 
Back
Top