How to Remove Apostrophes in Front of Numbers?

Gray Emberland

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?

Toufong Vang

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!

Gray Emberland

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

G. Felberbaum

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

Stacy Meadows

https://community.smartsheet.com/discussion/comment/331166#Comment_331166

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 🙏