Avatar of Amanda Walshaw
Amanda WalshawFlag 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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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 ?
Avatar of Gary Benjamin
Gary Benjamin
Flag of Canada image

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.)
Avatar of kenfcamp
kenfcamp
Flag of United States of America image

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

User generated image
and it should auto-increment the listings

User generated image
SOLUTION
Avatar of Gary Benjamin
Gary Benjamin
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of kenfcamp
kenfcamp
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Gary Benjamin
Gary Benjamin
Flag of Canada image

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.
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo