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

GreenBoy

New member
Joined
Apr 13, 2015
Messages
26
Reaction score
0
Points
0
Excel Version(s)
Office 365
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
 
Ok - so fresh Coffee was what i needed.

The solution is a lot simpler than i was thinking,

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

Seems to work , so thanks for reading.
Dave
 
Hi GreenBoy,

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

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

Cheers,
 
Why not something along the lines of:
.Range("A1").Value + 4000000
 
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
 
A bit late to this one...but...Maybe this?:
Code:
Number.ToText([CSR Number],"400000")
 
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 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.
 
Back
Top