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

"

Copy then EditPaste SpecialValuesOKEsc.

Then F2ENTER 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

On Wed, 21 Nov 2007 11:43:38 -0800 (PST), Syb wrote:

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

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




"Syb" wrote in message
...
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

On Nov 21, 3:19 pm, Gord Dibben <gorddibbATshawDOTca wrote:
"

Copy then EditPaste SpecialValuesOKEsc.

Then F2ENTER 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

On Wed, 21 Nov 2007 11:43:38 -0800 (PST), Syb wrote:
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

great stuff! worked like a charm, thanks!