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

Liangatang

New Member
Joined
Dec 10, 2009
Messages
7
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!
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

TheNoocH

Well-known Member
Joined
Oct 6, 2004
Messages
3,482
Welcome to the board...

try something like this

Code:
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
ABC
1Assume this is what it looks like originallyAssume this is what you want it to look like after the code
2Username = johnUsername = john
3Email =Email = john@here.com
4Username = janeUsername = jane
5Email =Email = jane@here.com
Sheet7
Excel 2007
 
Last edited:

Liangatang

New Member
Joined
Dec 10, 2009
Messages
7
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

Well-known Member
Joined
Oct 6, 2004
Messages
3,482
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
ABCD
1Assume this is what it looks like originallyAssume this is what you want it to look like after the codeOr via formula
2Username = johnUsername = johnUsername = john
3Email =Email = john@here.comEmail = john@here.com
4Username = janeUsername = janeUsername = jane
5Email =Email = jane@here.comEmail = jane@here.com
Sheet7
Excel 2007
Cell Formulas
RangeFormula
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)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,622
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top