add emails to usernames? (add the same text to multiple cells)

[Liangatang]

Hey hey-

I'm creating a csv to import usernames, passwords, and email addresses into our database. I need to add "@here.com" to all the usernames to populate the email address column. Any simple way of doing this?

Example:
Username = john
Email = ________(blank)
Username = jane
Email = ________(blank)

I need the email column to automatically populate:
Email = john@here.com
Email = jan@here.com

Since we're dealing with about 500 users, typing each email in by hand seems ridiculous! Thanks in advance for the help!

 

[TheNoocH]

Welcome to the board...

try something like this

Sub test()
   sEmail = "@here.com"
   For Each cell In Range(Cells(1, "A"), Cells(Cells(Rows.Count, "A").End(xlUp).Row, "A"))
      If Left(cell.Value, 8) = "Username" Then sName = Trim(Right(cell.Value, (Len(cell.Value) - InStr(1, cell.Value, "="))))
      If Left(cell.Value, 5) = "Email" Then cell.Value = "Email = " & sName & sEmail
   Next cell
End Sub

Excel Workbook
	A	B	C
1	Assume this is what it looks like originally		Assume this is what you want it to look like after the code
2	Username = john		Username = john
3	Email =		Email = john@here.com
4	Username = jane		Username = jane
5	Email =		Email = jane@here.com
Sheet7

Excel 2007

 

[Liangatang]

Sorry - I'm doing this in MS excel 2007. I'm not familiar with the coding you sent - but it does look helpful! Can this be done in excel 07?

 

[TheNoocH]

yes this is VBA code...NOT a formula...

if my assumptions above are correct then follow the instructions in my signature to install the code...


I also added a formula option...

Excel Workbook
	A	B	C	D
1	Assume this is what it looks like originally		Assume this is what you want it to look like after the code	Or via formula
2	Username = john		Username = john	Username = john
3	Email =		Email = john@here.com	Email = john@here.com
4	Username = jane		Username = jane	Username = jane
5	Email =		Email = jane@here.com	Email = jane@here.com
Sheet7

Excel 2007

Cell Formulas
Range	Formula
D2	=IF(LEFT(A2,5)="Email",A2 & TRIM(RIGHT(A1,LEN(A1)-FIND("=",A1,1)))&"@here.com",A2)
D3	=IF(LEFT(A3,5)="Email",A3 & TRIM(RIGHT(A2,LEN(A2)-FIND("=",A2,1)))&"@here.com",A3)
D4	=IF(LEFT(A4,5)="Email",A4 & TRIM(RIGHT(A3,LEN(A3)-FIND("=",A3,1)))&"@here.com",A4)
D5	=IF(LEFT(A5,5)="Email",A5 & TRIM(RIGHT(A4,LEN(A4)-FIND("=",A4,1)))&"@here.com",A5)

 

[Liangatang]

you're amazing!