Skip to content
  • Try for free

    • 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.

    How to Remove Apostrophes in Front of Numbers?

    ✭✭
    in Formulas and Functions

    Hi all,

    My problem is SS has put in apostrophes in front of numbers after importing from excel. I have a column that is comprised of both text and numerical values. I've tried using a value function to remove the apostrophes. This works for the numerical values but shows errors on any cell containing text. Does anyone have advice for an IF statement I could use to both delete the apostrophes and keep the cells with text in them how they're supposed to be?

    · Share on FacebookShare on Twitter

    Sign in
    Try for free

    Answers

    • ✭✭✭✭✭

      Below are two approaches to accomplishing that.


      (1) Use ISNUMBER() to evaluate the cell before trying to get the value of its content.

      = IF( ISNUMBER([Column from Excel]@row), VALUE([Column from Excel]@row), [Column from Excel]@row)


      (2) Leverage the error that is returned when trying to get the VALUE() of a string/text. Do this by using the function IFERROR().

      = IFERROR( VALUE([Column from Excel]@row), [Column from Excel]@row ) 

      Cheers!

      · Share on FacebookShare on Twitter
    • ✭✭

      Yes! That works except I changed ISNUMBER to ISTEXT. Thank you.

      · Share on FacebookShare on Twitter
    • ✭✭

      I have the same issue on a zip code so I changed the column type to date and back to text/number


      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      I have tried evry solution on here and this is the one that worked unfortunately some of my numbers changed to dates....

      So I just went back a few steps and made a duplicate column and did as you said... this way I could still refer to the original numbers for the ones that stayed as dates 😀

      Thank you so much 🙏

      · Share on FacebookShare on Twitter

    Sign in
    Try for free

    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