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 user.001@example.com, user.002@example.com.

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 user.001@example.com. 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

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

user001@example.com
user002@example.com
user003@example.com
user004@example.com
user005@example.com
...
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 '13 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 '13 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 '13 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 '13 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 '13 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.

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.