How to add an email address in Google Sheets/Excel?

April 8, 2021 12:49 AM

I have a Google sheet with a column of data that contains a block of numbers and letters in each cell, like E4U8BN. There are hundreds of them, each representing a single person. I want to tag the same email address, as in "@email.com" at the end of each of those blocks. I could manually paste them on, but is there a way to automate this? A formula, perhaps?

In the end the blocks should be [e-mail]. Seems like a simple equation would do the trick but I can't figure it out. Ideally I'd like to keep it in Google Sheets but Excel would work, too.

posted by zardoz to Computers & Internet (9 answers total)

Best answer: You want the concatenate function! https://spreadsheetplanet.com/add-text-to-beginning-or-end-of-cells-excel/
posted by idlethink at 1:03 AM on April 8, 2021 [7 favorites]

Response by poster: That did it, idlethink! You the best!
posted by zardoz at 1:11 AM on April 8, 2021 [1 favorite]

Best answer: If this is a one time thing rather than an ongoing processing requirement, and if the data in the column are just raw data rather than formula results, you can do it with search and replace.

Click the column letter to select the entire column, choose "Find and Replace" from the Edit menu, search for $, replace with @email.com, tick the "Search using regular expressions" checkbox, and click the "Replace all" button.

This works because $ in a regular expression means "at the end of the text". Adding $ to the end of a regex makes the rest of it match only at the end of the text being searched. In this particular instance there isn't a rest of it, so the regex just matches the empty string right at the end of each of the cells being searched.
posted by flabdablet at 1:23 AM on April 8, 2021 [6 favorites]

For future reference: if you ever need to do a similar search and replace in order to prepend rather than append text to all cells in a range, use ^ instead of $ as the search regex.

Inserting ^ before the start of a regex makes the rest of it match only at the beginning of the text being searched. As before, there isn't a rest of it in this case, so the regex just matches the empty string at the beginning of each cell searched, and that's what the replacement text gets substituted for. And as before, the substitution isn't actually replacing anything else, so it amounts to straight insertion.

You can combine these: the regex ^$ is constrained to match at the beginning and end of the text being searched, and because there's nothing at all between the constraint markers, will match only text that's completely empty. This is a quick way to get a non-empty default value substituted into all the empty cells within a range.

These three simple regexes are the ones I've used more often than any other over the years, and knowing them has saved me a lot of time.
posted by flabdablet at 1:52 AM on April 8, 2021 [6 favorites]

something slightly dumber than the excellent answers above would be just using the append functionality that's in Excel. I can't tell if there's something in your use case that requires NOT adding a new column with the email address you want.

If the E4U8BN is in cell A1, you can go into cell B1 and type =A1&"@email.com" and that should make B1 say [e-mail]; you can drag that formula all the way down and BOOM you're done. And you don't have to talk about regex with anybody, which avoids this issue https://xkcd.com/1171/.
posted by adekllny at 10:17 AM on April 8, 2021 [1 favorite]

Use of the & operator is covered in the page that idlethink linked above.
posted by flabdablet at 11:04 AM on April 8, 2021 [1 favorite]

For future reference, here's perhaps the dumbest solution of all, with no formulas or regexes:

* Copy the column of data to the clipboard
* Paste into a text editor
* Copy a carriage return to the clipboard
* Search-and-replace carriage return with "@email.com[carriage return]"
* Copy the contents of the text editor and paste back into the spreadsheet
posted by aws17576 at 1:30 PM on April 8, 2021

If you go that route, make sure that the text editor has actually added a carriage return to the end of the last line before doing the search and replace step. Add an extra one yourself if unsure. Also make sure the text editor actually lets you paste a carriage return into the search and replace boxes; some don't.

Personally I think just learning those three super-simple regexes is less work. I have yet to encounter a modern spreadsheet that doesn't support a regex option in search and replace.
posted by flabdablet at 2:54 PM on April 8, 2021

Bah. I just encountered Excel.

Oh well. At least there's an add-in for 32-bit Windows versions that drags them into the 20th century.
posted by flabdablet at 3:09 PM on April 8, 2021