How may I extract only the numbers from a cell?

Options

I need to extract 16 and 9.

As you can see the number characthers may change (I may have 1-2 digits) but always at the beginning.

Thanks

Best Answer

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭
    Answer ✓
    Options

    Hey @Enrico Trovato, welcome to the community!

    Couple of questions for you:

    1. Does the cell need to be split at that number (i.e. "16" goes in Column1, "A STD RB" goes in Column2)? Or is it okay if it stays in that cell?
    2. Are there any more numbers besides 16 and 9?


    If it's okay for the "16" or "9" to stay in that cell and the only numbers are 16 and 9, then this formula works to get either 16 or 9 in the column.

    (replace "ColumnName" with your column's name):

    =IF(CONTAINS("16", ColumnName@row), 16, IF(CONTAINS("9", ColumnName@row), 9, ""))
    

    Otherwise, if you have more numbers besides 16 and 9 and you'd like to extract them, you do something like:

    =IFERROR(
        IF(
            AND(VALUE(LEFT(ColumnName@row, 2)) > 0), LEFT(ColumnName@row, 2)), 
            
        IFERROR(
            IF(
                AND(VALUE(LEFT(ColumnName@row, 1)) > 0, ISTEXT(MID(ColumnName@row, 2, 1))), 
                    VALUE(LEFT(ColumnName@row, 1)), 
                        ""), 
    ""))
    

    This basically says "If the value of the first two characters are greater than 0, show me that value. But if there's an error (i.e. "9A" throws an error because it's not a number), then show me the value of the first character.

    This will only work with two digits. You'd have to expand this formula to work with more.


    You can copy/paste the formulas from here in the cells.


    If this answered your question, please select "Yes" below - it helps others in the community find solutions they are looking for and random Googlers out there 👀

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Answers

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭
    Answer ✓
    Options

    Hey @Enrico Trovato, welcome to the community!

    Couple of questions for you:

    1. Does the cell need to be split at that number (i.e. "16" goes in Column1, "A STD RB" goes in Column2)? Or is it okay if it stays in that cell?
    2. Are there any more numbers besides 16 and 9?


    If it's okay for the "16" or "9" to stay in that cell and the only numbers are 16 and 9, then this formula works to get either 16 or 9 in the column.

    (replace "ColumnName" with your column's name):

    =IF(CONTAINS("16", ColumnName@row), 16, IF(CONTAINS("9", ColumnName@row), 9, ""))
    

    Otherwise, if you have more numbers besides 16 and 9 and you'd like to extract them, you do something like:

    =IFERROR(
        IF(
            AND(VALUE(LEFT(ColumnName@row, 2)) > 0), LEFT(ColumnName@row, 2)), 
            
        IFERROR(
            IF(
                AND(VALUE(LEFT(ColumnName@row, 1)) > 0, ISTEXT(MID(ColumnName@row, 2, 1))), 
                    VALUE(LEFT(ColumnName@row, 1)), 
                        ""), 
    ""))
    

    This basically says "If the value of the first two characters are greater than 0, show me that value. But if there's an error (i.e. "9A" throws an error because it's not a number), then show me the value of the first character.

    This will only work with two digits. You'd have to expand this formula to work with more.


    You can copy/paste the formulas from here in the cells.


    If this answered your question, please select "Yes" below - it helps others in the community find solutions they are looking for and random Googlers out there 👀

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • Enrico Trovato
    Options

    @Brett Wyrick works perfectly!!

    Thanks a lot per your precious help

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!