Adding Suffix to a value if it starts with X and is X Length???

[jilbobagins]

Hello All,

I have an issue on a field (Part Number) where it should be 10 Characters long and start with a 0. However sometimes the 0 is missing, however the next digit is always an 8 at 9 Characters long.

ie. 0844455468

or sometimes ie. 844455468


I have a solution which I filter by values starting with 8, then Filter by Text.Length and then finally add a prefix of 0 and then I have to re-merge them all.

There must be a better way either conditionally or within a custom calculation??

Hoping you can help me !!

Cheers

 

[cyborgski]

Use can use the PadStart function:

Text.PadStart([Part],10,"0")

Regards,
Mike

 

[jilbobagins]

Ohhhh I like that one!!!

How would I configure it to only work on values beginning with 8??

 

[cyborgski]

Text.PadStart([Part],10,"0") will left fill your data with 0 for up to 10 characters. If the part number is already 10 digits long, then nothing will be done to it. If the part number is 9 digits long then a zero will be put in front of it. You would just need to add as a custom column.

 

[jilbobagins]

Sorry Mike, I’m confusing you. What I mean is I only want it to effect values that are 9 characters long and begin with an “8”

 

[cyborgski]

No problem jilbo, still not sure if i understand, but something along the lines of if Text.Length([Part]) = 9 and Text.At([Part],0) = "8" then Text.PadStart([Part],10,"0") else [Part] should work too.

Sample is attached.

 

[jilbobagins]

I’m going to give that a try first thing tomorrow! I’m hoping it all starts to ‘click’ with me so I can start giving back to this forum !! Thanks again mike