auto fill custom format numbers

**NDBC** · 10-15-2009, 02:11 AM

I have the following list of numbers

0000 0000 0000 0000 0000 0100
0000 0000 0000 0000 0000 0101
0000 0000 0000 0000 0000 0102

and I need to continue the list to

0000 0000 0000 0000 0000 0326

I tried dragging by the grip in the bottom right hand corner but it changes the first didit only.

I tried to put it the numbers in the format

100
101
102

and using a custom format number to put in the extra zeros. This worked fine until I used a countif (or it may of been a lookup) statement in my code to look for 0000 0000 0000 0000 0000 0100 and it can't find it as the number is 100.

Is there away around this that will save me typing in an awful lot of zeros.

Thanks

**DonkeyOte** · 10-15-2009, 02:25 AM

Given limit of 326... if you need the physical values to be text values

A1: 0000 0000 0000 0000 0000 0101

Then

A2: =TEXT(1+SUBSTITUTE(A1," ",""),"0000 0000 0000 0000 0000 0000")
copied down

If on the other hand the values can be stored as numbers then set

A1: 101

Then

A2: =1+A1
copied down
apply a Custom Format to A1:226 of: 0000 0000 0000 0000 0000 0000

**Mikeopolo** · 10-15-2009, 02:26 AM

In C1 I have 99
In C2 I have "0000 0000 0000 0000 "&C$1+ROW()-1
Copy the formula in C2 down as far as required.

I've used the result in a lookup thus:
=VLOOKUP("0000 0000 0000 0000 104",data,2,FALSE)
where data is the name for the range of these numbers plus one column to the right,
and a value in that column is returned.

hth
Mike