Pulling chr string from Cell

[Weeble]

Hello, Im trying to make a list of E-Mail address for the company. I have imported the data from a database and have a spreadsheet with Cell A1 down with first names and cells A2 and down with Last Names. I would Like column A3 and down to Contain the first leter of A1 and the whole word from A2 + '@webaddress.com'. I don't know how to pull the first char from A1 and add the three together. Everything I have tried gives me a #value!.
Thanks,
Weeble

 

[Databaseguy]

Actually, I'd put first name in column A, last Name in columnB, and this formula in column C.


=LEFT(A1,1)&B1&"@webaddress.com"

You can tweak it to fit your needs. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[DaleWatson123321]

Hi Weeble,

Enter this formula into cell C2:

=LEFT(A2,1)&B2&"@webaddress.com"

If you have any problems, please advise.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[logius]

Well, I was bored, so here's a macro that'll do it for ya. I've been using the

Offset

function a lot lately, so I figured there'd be a way to do it with that.

Sub SetEmail()
Dim CellItem As Object
Dim FirstLtr As String

    'Runs through each cell within the range specified
    For Each CellItem In Range(Range("A1"), _
                               Range("A1").End(xlDown))

        'Number of "x" specifies how many chars to parse
        CellItem.Parse "[x]", CellItem.Offset(0, 2)

        'Holds the first letter of the cell
        FirstLtr = CellItem.Offset(0, 2).Value

        'Setting destination cell
        CellItem.Offset(0, 2).Value = FirstLtr & _
                        CellItem.Offset(0, 1).Value _
                        & "@webaddress.com"
    Next CellItem
    
End Sub

Just dump this into a module and run it.

 

[Weeble]

Thanks all, It Worked great. I did both just for fun and learning. Thanks, again.