Getting a base number out of a list

RossL
RossL ✭✭✭✭✭✭

Hi I could use some help with this list of numbers.

what i am looking to do is come up with a formula that will return 1234 no mater how it is entered. 1234 is just an example number but it could be 52368 or 65875 but it will always be a numeric value needs to be returned. it could have any number of letters or leading zeros in front of it and any number of letters after that would need to be stripped away. Is there a way to do this?

Tags:

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What is the maximum number of digits in any given string? Will you ALWAYS want to get rid of zeros, or would you want to keep them if they are in the middle of the number you want to pull?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • RossL
    RossL ✭✭✭✭✭✭
    edited 06/24/20

    @Paul Newcome

    I would only want to get rid of any leading zeros and non numeric digits on both ends of the part number.

    here is a sample list of what I have. I would only want the main number Example for the first one in the list below it would be 49986 and not H049986XB. the second would be 902404 and not H902404XA.


    H049986XB

    H902404XA

    46995

    45939

    052089XK

    052428

    46995XL

    h052147xa

    H902397XA

    52518

    52518

    052514s

    052515S

    049462S

    SAMPLE BLANKS

    H036679AE

    H036679AE

    902125

    45939

    47013

    52038

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    And just to be clear...

    052514s would be 52514

    H036679AE would be 36679

    52038 would be 52038


    Are those correct?


    I have a few ideas but need to do some testing.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • RossL
    RossL ✭✭✭✭✭✭

    Yes that is correct.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What is the longest string you anticipate?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • RossL
    RossL ✭✭✭✭✭✭

    the longest string i would anticipate would be 10 digits.

  • RossL
    RossL ✭✭✭✭✭✭

    @Paul Newcome

    this will do the trick, Thanks for the help!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!