Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Extract 10-digit string based on consistent lead-in digits

    ✭✭✭
    in Formulas and Functions

    I have a number I'm trying to extract from a column and I can't figure out how. The number is *always* 10 digits, *always* starts with 50000, and can appear *anywhere* in the cell in Column A (start, middle or end).

    Column A has a mock-up of the type of data I'm working with. The Result column shows what I want to achieve: a 10-digit number based on the 50000##### number located anywhere in Column A.

    I know I need to use FIND, and I've looked at similar Q & A in the Community, but can't land on an answer. Thoughts? Thanks!


    image.png


    · Share on FacebookShare on Twitter

    Best Answer

    • ✭✭✭✭✭✭
      Answer ✓

      Hi @Alex888,

      As long as "5" doesn't appear in the words prior to the digit of numbers, you could use this:

      • =MID([Column A]@row, FIND("5", [Column A]@row), 10)

      Hope this helps!

      Best,

      Zach Hall

      Training Delivery Manager / Charter Communications

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭✭
      Answer ✓

      Hi @Alex888,

      As long as "5" doesn't appear in the words prior to the digit of numbers, you could use this:

      • =MID([Column A]@row, FIND("5", [Column A]@row), 10)

      Hope this helps!

      Best,

      Zach Hall

      Training Delivery Manager / Charter Communications

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Thanks!! That was just what I needed to get straight - my final formula I'm using (to only use 50000 numbers and to make to value is): =VALUE(MID([Column A]@row, FIND("50000", [Column A]@row), 10)) Thanks, again!!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions