Append Dynamic Value (10000 possibilities: from 0000 to 9999) to Static Value

Hi All,

As title of post is saying, I would need some help please with very detailed step by step instructions for Excel Professional Plus 2013 (windows 10). I have a static value which consist of several digits. Static value is all the time the same - it doesn't change.

At the end of static value, without any blank space added, I would like to add all 10000 values between 0000 and 9999 including said two. After adding each one should go to new line but in the same column. Example: if static value is ''01'' then lines in the same column would be:

010000
010001
010002
010003
010004
...
019999

I got idea that I should put those values in two separated neighbour columns, one 10000 lines with static value and another column each line containing value from 0000 to 0001 but its not working as first problem occurs already when typing ''0000'' without the quotes because it gets automatically converted to ''0'' without the quotes. Additional problem is that I tried to use function similar to ''=A1&B1'' without the quotes or ''=A1 & B1'' without the quotes in the third column, obviously on the same line but nothing occurred in the line of third column.

Detailed step by step explanation would be much appreciated, thank you!

If your first value (e.g. 01) is in A1, then you can put this formula in A2:

=$A$1&TEXT(ROWS($1:1)-1,"0000")

then copy that down to A10001.

Hope this helps.

Pete

I should not be typing "0000" without the quotes only because this is dynamic and not static. To each static value, dynamic ones from 0000 to 9999 should be appended at the end without space added. I tried what you said but it didn't work. It didn't even create any formula at all but I only ended up having in the cell exactly what you typed.

The cell might be formatted as Text, so it shows exactly what you have typed in. Format the cell as General, then press F2 as if to edit it and then press < Enter >.

Then you can copy the formula down.

Hope this helps.

Pete

doesn't work If changing to General then 0000 gets converted to 0

formula doesn't work.

newdoverman thank you for reply. Its not working. I tried to enter in B1 what you said. After trying to double click that B1 cell, the value of said cell finally starts acting as a formula. However when I press Enter key, I get error with the content similar to this one:

"if typing 1 + 1, cell shows: 2
if you want to avoid that, add single quotation mark BEFORE them
so if you type '=1+1, cell shows: =1+1"

This is exact content of error message. Unfortunately it doesn't say what is considered with a word ''them''

To clarify: Only text I had in entire table is value which i expect to be STATIC one in A1 and formula in B1. Nothing else was added anywhere.

Originally Posted by Tony Valko

Here's a small sample file that demonstrates this.

This is what I wanted to have result in. Exactly this. See my reply to newdoverman why it didn't work. I didn't have anything added in B column, just wanted to add the formula in B1. That could be the reason but I think nothing should be in B column at all since I was supposed to type formula A1. No idea what I was doing wrong but thank you very much for your valuable time for giving me that file.

I don't know what the regional settings for the moon are, but if you are really in continental Europe then you might need to use a semicolon ( ; ) instead of the comma ( , ) in the formula.

Hope this helps.

Pete