Avatar of Amanda Walshaw
Amanda Walshaw
Flag for Australia asked on

Extending email address with unique number template

I need to copy down these email address in excel but its not increasing the number as I expected please see attached file
I cannot use real email addresses I must use dummy ones I have 455 lines to update
excel-question.xlsx
* Excel TableMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Gary Benjamin

8/22/2022 - Mon
Ryan Chong

I don't quite get your problem and requirement...

I need to copy down these email address in excel

are you trying to copy down those email addresses out? copy down and then ... ?

do you want to combine values such as A2&" "&B2 , etc ?
Gary Benjamin

I am guessing that your only requirement is to generate 455 rows in column F with email addresses like
1mailtest@mail.com.au
2mailtest@mail.com.au
3mailtest@mail.com.au
4mailtest@mail.com.au
...
9mailtest@mail.com.au
10mailtest@mail.com.au
11mailtest@mail.com.au
12mailtest@mail.com.au
...
454mailtest@mail.com.au
455mailtest@mail.com.au

and that we can ignore everything else in your sample workbook, and in particular, the email addresses in column C and the blank cells in columns A and B which you refer to in your comment I cannot use the  A2&" "&B2.

You indicate you have tried (and failed) to do this by copying down, which I interpret to mean that you selected the first few values in column F and then tried to autofill by dragging the bottom right corner of the selected range.

You have not indicated if it is OK or not to have formulas in the cells, but what if you put this formula into cell $F$1

=HYPERLINK("mailto:"&ROW(F1)&"mailtest@mail.com.au",ROW(F1)&"mailtest@mail.com.au")

Open in new window


and then copied $F$1 to the range $F$2:$F$455 ?

This looks and functions like the constants in your sample workbook, but the cells will contain formulas.

If you must have constants in the cells, then after you generate the addresses select the entire range $F$1:$F$455, copy, and paste-values.

(I have hard coded "mailtest@mail.com.au" in the formula, but it would be a better practice to store this in a cell, say $A$1, name that cell something like "EmailSuffixAndDomain" and then refer to "EmailSuffixAndDomain" in the formula.)
kenfcamp

This is simple enough, all you really need to do is select two columns in order

Go to the bottom right corner of the last selected column click and hold your left mouse button and drag down to the number of rows (items) you need auto populated

sshot1.png
and it should auto-increment the listings

sshot2.png
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Gary Benjamin

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gary Benjamin

I think that both I and kenfcamp provided solutions to Amanda's requirement. Furthermore, our answers cover some quirks in Excel's fill capabilities in recent versions (2016+) that users of older versions of Excel (2013-) may one day find useful, such as:
  • There is a difference between Autofill and Flash fill.
  • The default fill option available by dragging the bottom right corner of the cell range is dependent on the version of Excel.
  • Flash fill produces different results if the cells to the left or right of the range being filled has data or not.
I think that this question should be closed as solved, with half points to me and half to kenfcamp.