4

How can I add @gmail.com or something similar to a column of usernames without having to type this into each cell? I am using Excel 2010. I have a list of several hundred usernames that need to have @gmail.com or something similar added to create a csv file for upload.

10

Assuming your names are in column starting from A1, you can use the formula:

=A1&"@gmail.com"
| improve this answer | |
  • 1
    +1 - But could also make cell Ax = "@gmail.com" and make the formula =A1&Ax. Then if change required, only one cell to change – Wayne Sep 13 '11 at 3:47
  • @Wayne, I was thinking the same thing just after I posted this but thought I'd keep it simple. Also, by Ax do you mean B1 and =A1&B1? – Hand-E-Food Sep 13 '11 at 4:57
  • I was meaning A<somecol> (as in any column) maybe a small italic n would have been better suited. – Wayne Sep 13 '11 at 13:23
  • Ah. Columns are lettered and rows are numbered. A represents the column, so A1 and Ax are in the same column. That's where my confusion was with your comment. – Hand-E-Food Sep 14 '11 at 4:24
3

If you need to change the value of the cells, I'd recommend Hand-E-Food's solution. (If you need to keep it in one column, just Copy, then Paste Special > Values back to the original column.)

However, if you want it to simply display the suffix without actually altering the cells' values, you can use a custom number format instead. In Excel 2007 or later, select Custom in Home > Number. In earlier versions, it's Format > Cells > Number. Use @"@gmail.com" as the custom format.

In this screen shot, column A is using a custom format as I describe. Column B is using Hand's solution (you can see the formula in the center right).

Excel custom formatting example

| improve this answer | |
2

Let's say your usernames are in column A starting from A1. Go to B1, enter the following formula and drag it down till the end of the row where the list ends (or you can simply double click on the lower right corner of the cell).

=CONCATENATE(A1,"@gmail.com")
| improve this answer | |
0

A very easy way with notepad++: If you need to make modification on a column, then:

  1. copy paste your excel colum on notepad++
  2. press alt + the arrows.
  3. what you will write will be add in all lines
| improve this answer | |
0

If you want to add specified text in each cells of a selection directly, the following VBA macro will ease your work.

Step 1: Select the range in which you will add specified text;

Step 2: Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

Step 3: Click Insert > Module, and paste the following VBA code in the Module Window.

Sub AppendToExistingOnRight()
Dim c as range
For each c in Selection
If c.value <> "" Then c.value = c.value & "@gmail.com" 
Next
End Sub

Step 4: Press the F5 key to run this macro.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.