How to Remove Apostrophes in Front of Numbers?

Gray Emberland · 06/29/21

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 · 06/29/21

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 · 06/29/21

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

G. Felberbaum · 05/26/22

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

Stacy Meadows · 04/11/23

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 🙏

How to Remove Apostrophes in Front of Numbers?

Answers

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

Help Article Resources

Categories