# formulas and email

Syb
Guest
Posts: n/a

 21st Nov 2007
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

Gord Dibben
Guest
Posts: n/a

 21st Nov 2007
=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.

'David McRitchie
Dim Cell As Range
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
With Worksheets(1)
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

Conan Kelly
Guest
Posts: n/a

 21st Nov 2007
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.

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

Syb
Guest
Posts: n/a

 21st Nov 2007
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.
>
> 'David McRitchie
> Dim Cell As Range
> For Each Cell In Intersect(Selection, _
> Selection.SpecialCells(xlConstants, xlTextValues))
> With Worksheets(1)
> 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!

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Brand Microsoft Excel Worksheet Functions 1 10th Oct 2009 01:01 PM =?Utf-8?B?c3BpY29sZQ==?= Microsoft Excel Worksheet Functions 2 4th Apr 2007 02:01 AM Claus Microsoft Excel Programming 2 7th Sep 2005 02:40 PM Microsoft Excel Misc 0 29th Jul 2004 04:43 PM fruvous333 Microsoft Excel Worksheet Functions 1 25th Nov 2003 12:29 AM

Features