Results 1 to 7 of 7

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

  1. #1
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    49
    Articles
    0
    Excel Version
    2016

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    60
    Articles
    0
    Excel Version
    Office 365
    Use can use the PadStart function:

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

    Regards,
    Mike

  3. #3
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    49
    Articles
    0
    Excel Version
    2016
    Ohhhh I like that one!!! 👍👍

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

  4. #4
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    60
    Articles
    0
    Excel Version
    Office 365
    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 by cyborgski; 2019-10-10 at 08:52 AM.

  5. #5
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    49
    Articles
    0
    Excel Version
    2016
    Sorry Mike, Im confusing you. What I mean is I only want it to effect values that are 9 characters long and begin with an 8

  6. #6
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    60
    Articles
    0
    Excel Version
    Office 365
    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.
    Attached Files Attached Files

  7. #7
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    49
    Articles
    0
    Excel Version
    2016
    Im going to give that a try first thing tomorrow! Im hoping it all starts to click with me so I can start giving back to this forum !! Thanks again mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •