Append the number 4 and enough Zeros to make all numbers 6 digits long

[GreenBoy]

Im having a very long week and i think my brain needs re-booting.

I know this should be fairly easy but for the life of me i cant get this to work.

I have a series of site numbers which range from 1 to 5 digits in length - i.e. 1 to 99999

What i need to do is make them all 6 digits long and start with a 4, so 1 would become 400001 and 175 would be 400175 all the way up to 499999

Can any kind person point me at the right functions to do this?

I am assuming Text.PadStart is used but need to calc the number of zeros needed and then do the same for a single number 4 at the start, but the calculating the number of Zeros to add to dynamically add a different amount per site number is escaping me.

Thanks in advance for any assistance, comments etc
Dave

 

[GreenBoy]

Ok - so fresh Coffee was what i needed.

The solution is a lot simpler than i was thinking,

Text.PadStart(Text.PadStart(Number.ToText([CSR Number]),5,"0"),6,"4")

Seems to work , so thanks for reading.
Dave

 

[Ken Puls]

Hi GreenBoy,

You can simplify this a bit, avoiding a second call to the Text.PadStart function:

"4" & [COLOR=#333333]Text.PadStart(Number.ToText([CSR Number]),5,"0")[/COLOR]

Cheers,

 

[macropod]

Why not something along the lines of:
.Range("A1").Value + 4000000

 

[GreenBoy]

Ken and Macropod - thanks for the replied - both work great.

I did say it was a long week !!

Thanks for the solutions - you are awesome!.
Dave

 

[Ron Coderre]

A bit late to this one...but...Maybe this?:

Number.ToText([CSR Number],"400000")

 

[Ken Puls]

Ron, that's a clever one!

 

[amyfb]

I would like to Text.Padstart with a 0 whenever there is a 4 digit number. My stab at the if then else didn't work. Any tips? if Text.Length [zipcode] = 4 then Text.PadStart([zipcode],5,0 else [zipcode])

 

[amyfb]

I would like to Text.Padstart with a 0 whenever there is a 4 digit number. My stab at the if then else didn't work. Any tips? if Text.Length [zipcode] = 4 then Text.PadStart([zipcode],5,0 else [zipcode])

I was happy (and surprised) to find that my goal was accomplished by just using the Text.Padstart arguments. I love PQ more every time I touch it.