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

jilbobagins

Member
Joined
Apr 11, 2019
Messages
80
Reaction score
0
Points
6
Excel Version(s)
2016
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
 
Use can use the PadStart function:

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

Regards,
Mike
 
Ohhhh I like that one!!! 👍👍

How would I configure it to only work on values beginning with 8??
 
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.
 
Last edited:
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”
 
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.
 

Attachments

  • PartPad.xlsx
    16.3 KB · Views: 11
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
 
Back
Top