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

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 , A3 to read
, A4 to read - 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

",A2&"@t hislocation.com")

copy down

Regards,

Peo Sjoblom

"Sue" wrote:

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 , A3 to read
, A4 to read - 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

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 & ". Copy down with Fill
Handle. You should see . 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.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Sue" wrote in message
...
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 , A3 to read
, A4 to read - 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

Sue

Assuming names are in column A.

In B1 enter =A1 & "

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

Copy column B and in place Paste SpecialValuesOKEsc.

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

On Thu, 13 Jan 2005 13:03:05 -0800, Sue wrote:

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 , A3 to read
, A4 to read - 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