formulas and email

  • Thread starter Thread starter Syb
  • Start date Start date
S

Syb

Is the following scenario possible?

A1 = Smith
A2 = John

I want to take the first letter of cell A2 (J), combine it with
everything from A1 (Smith) and then add @gmail.com. I'm going through
some database stuff that is all in spreadsheets and emails are not
given.

Any thoughts would be appreciated.

Jeff
 
=LEFT(A2,1)&A1&"@gmail.com"

Copy then Edit>Paste Special>Values>OK>Esc.

Then F2>ENTER to change to a real email address.

If you have many to change to hyperlinks use David McRitchie's 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


Gord Dibben MS Excel MVP
 
Syb,

Try this:

=HYPERLINK("mailto:" & LEFT(A1,1) & A2 & "@gmail",LEFT(A1,1) & A2 &
"@gmail")

I don't know if capital letters will affect things. You may have to adjust
the formula to make letters all lower case.

lookup the HYPERLINK() function in help for more info.

HTH,

Conan Kelly
 
=LEFT(A2,1)&A1&"@gmail.com"

Copy then Edit>Paste Special>Values>OK>Esc.

Then F2>ENTER to change to a real email address.

If you have many to change to hyperlinks use David McRitchie's 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

Gord Dibben MS Excel MVP
Click to expand...

great stuff! worked like a charm, thanks!
 
Back
Top