3

I'm putting together a long list of users where the user names have the format user001, user002 etc. To each user there is an e-mail address with the format [email protected], [email protected].

Now I would like to use the function in Excel that allows you to "drag-and-click" copy cells downward and increases the number by one for every cell when I'm going to enter the e-mail addresses. However, that only seems to work if the number is last in the string.

What I get now is just copies of [email protected]. Is there someway to make the number 001 increment even though it is inside a string or do I have to edit it all manually?

4 Answers 4

4

Perhaps you can work around with something like that:

A1: "user"
B1: 1
C1: "@example.com"
D1: =$A$1&TEXT(B1,"000")&$C$1

[Edit: This applies for the english version of Excel, otherwise the last line should read =$A$1&TEXT(B1;"000")&$C$1, note the semicolon instead of a comma. Thanks @Kevin for clarifiying this point.]

Then you can drag-and-click column B and D, where D will look like

[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
...
7
  • Unfortunally, the excel sheet is homegrown, heavily macroed and locked down and I don't have access to the password. If there is no way to do it without manipulating I'll just have to do it manually.
    – Sandokan
    Mar 1, 2013 at 9:42
  • Can't you create the list in a separate excel sheet and copy&paste it into the original one?
    – mpy
    Mar 1, 2013 at 9:48
  • Yes, trying that. Unfortunally, the formula given does not work. I get an error and then it marks the area inside the TEXT function.
    – Sandokan
    Mar 1, 2013 at 9:58
  • I changed the format specifier in my answer. Now it should also work in Excel 2010 according to techonthenet.com/excel/formulas/text.php. I can only test in LibreOffice.
    – mpy
    Mar 1, 2013 at 10:10
  • 2
    Semicolon is generally used in none english versions of Excel, english language versions use a comma. If this answer answered your question you should +1 the answer and mark it as an answer so that others dont come along and try to answer it again. Mar 1, 2013 at 11:53
3

Better use the below. This doesn't require to create a extra column with incremental values. If you follow the previous extra column(A1) then finally you have to remove that column and created values to be Copy & pasted (as values) again.

="user"&TEXT(ROW(), "000")&"@example.com"

="user"&TEXT(ROW()+100, "000")&"@example.com"
2

Could you use two columns, like this:

A1: user001
B1: =A1&"@example.com"

When you drag the two columns down, the string in column B will increase as you describe.

Not knowing what the limitation are on your sheet, I don't know if you can do this.

You could also try using an equation like this

A1: ="user"&COLUMN(A1)&"@example.com"

To get the correct number of 0s , you could use the format parameter in the TEXT(value, format_text) function.

0

I was looking for something related to this, see what I found:

=LEFT(A835,3)+1&"Ay"

If you have a text or serial or reference number like this

942Ay

And you don't want to copy and paste again and again, you can use above method by using the LEFT function, & and add 1 and then your text, whatever it is. You can also continue or add more text by using the & key without spaces and without an apostrophe.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .