Getting Excel to acknowledge zeroes at the start of large numbers

[PipBoy808]

Hi,

I'm looking to generate numbers in increments of 1 with the format 'U000000'.

So, the first number will be U000001, then U000002, and so on.

My initial plan was to separate the six digits from the letter U by using MID(Number, 2, 6) and then adding one.

So:

LastNumber = "U000000"
 
SeparateFromTheUandAddOne = (MID(LastNumber, 2, 6)+1)

Unfortunately, the above ignores the zeroes at the start of the number and takes 'Separate From the U and Add One' as '0+1=1' which later gives me the number U1. I need the number to maintain the same format throughout and increase in a way that is going to work when going from 'U000000' to 'U000001', but also from 'U009999' to 'U010000'.

Can anyone help me find a way that will get Excel to acknowledge the zeroes every time?

Thanks in advance

 

[iferror]

A1=1
A2=A1+1
and copy down

B1="U" & TEXT(A1;"000000")
and copy down

 

[iferror]

upps...there's no need to split the formula in two columns

A1="U" & TEXT(ROW();"000000") and copy down

 

[PipBoy808]

Since this was posted in the VBA forum, I figured it was implied that this is all happening in VBA and not in a worksheet, so I'm afraid that doesn't help. Thanks though.

 

[iferror]

My bad

Here's the same thing in VBA-ish

Sub Uand6digits()

For indx = 1 To 1234

  MsgBox ("U" & Format(indx, "000000"))

Next


End Sub

 

[SirJB7]

Hi, PipBoy808!

A shorter and much faster (depending on the range size) version:

Range("A1:A1000").Formula="=""U""&Text(Row(),""000000"")"

Regards!