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

On Wed, 21 Nov 2007 11:43:38 -0800 (PST), Syb <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
news:cab5efe8-a96b-47df-867d-(E-Mail Removed)...
> 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:
> =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
>
> On Wed, 21 Nov 2007 11:43:38 -0800 (PST), Syb <jeff.lecl...@gmail.com> 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!