fill problem in excel

Linbox

In Excel I often have to enter in one column this type of number eg


001
001A
002
002A
003
003A
004
004A
005
005A


The column is obviously formatted to TEXT.
How can you easily create this run down to say 050A.


Fill is the obvious answer but try before you post, it doesn’t work; the “A” numbers don’t change but the whole numbers do.

Tigsteroonie

In cell A1, type the number 1
In cell B1, type =A1&"A"

Highlight both, and then fill down. Works on mine.

Once you've got it in place, if you don't want the formula, then copy & paste special - values.

Not sure how you'll force the format to three digits with preceding zeros though.

leemoss

Tigsteroonie wrote: »

Not sure how you'll force the format to three digits with preceding zeros though.

You could then use the concatenate function

=CONCATENATE(B1,A1)

Cell B1= '00
Call A1= 1, 2 etc

Then simply copy, paste values

leemoss

You could simply start at 001 and drag down to your desired number, copy and paste them all again below the 1st lot of numbers and use the sort function to order them 001, 001, 002, 002 etc
Then use the concatenate function to add the a's every other cell.

edit: if you get stuck, I could upload an example spreadsheet.

Linbox

Thanks but none of these really fit the bill. Far to time consuming, its quicker to just type them out if formula or any manipulation such as sort are involved sort doesn't work either as it puts the whole numbers first then the "A" Numbers then the "B" numbers and so on.

These numbers have to be added to lots of different already existing spreadsheets all of which are different with different series of numbers.
Easyest way I have found is keep a spreadsheet with the series open and copy and paste as required; this is not not the ideal and a Pain to do - again too many actions.

Any other ideas?

Tigsteroonie

In top cell (eg A1) type '001
In cell below (eg A2) type =A1&"A" which will show as 001A
Highlight the two cells, use bottom right corner to fill down.

Not sure what OP means by "Far to time consuming, its quicker to just type them out if formula or any manipulation such as sort are involved". Only takes a couple of seconds to type in those two cells and then fill downwards.

Linbox

Tigsteroonie wrote: »

In top cell (eg A1) type '001
In cell below (eg A2) type =A1&"A" which will show as 001A
Highlight the two cells, use bottom right corner to fill down.
.

Thanks that's better. Nice and easy.

grizzly1911

Tigsteroonie wrote: »

In top cell (eg A1) type '001
In cell below (eg A2) type =A1&"A" which will show as 001A
Highlight the two cells, use bottom right corner to fill down.

Not sure what OP means by "Far to time consuming, its quicker to just type them out if formula or any manipulation such as sort are involved". Only takes a couple of seconds to type in those two cells and then fill downwards.

Always like to learn new stuff but should the formula be =(A1&"A")?

That's the only way I could get it to work in 2003.

Sorry to hijack but I had to reload excel after an OS problem. Each time I open a spreadsheet I could a dialogue box with "Loading excel feature" and a time bar. I can cancel the action and continue but anyway of stopping it showing?

Topcat_2

To getthe leading zeros, try
Format > Cells > Custom
Type 000 in the area on the right, then ok
This may work
TC x