#INVALID OPERATION - Apostrophe catastrophe

Hi Community,

I'm trying to convert some numbers expressed in KB into GB so I can sum all in GB. To do this I had to use the LEFT function to pull everything appended with "KB" from a column. That was no problem. I then took the result of that function and did a copy/paste special, so I would no longer have the formula, but the value. I then used another column to divide the KB by 1000000 in order to get GB. The result continues to be an #INVALID OPERATION error. I know it's not the syntax of the formula, because I can enter values manually and it works fine. Additionally, I've just noticed that there is a very small apostrophe preceding all of the numbers in the column where the #INVALID OPERATION error shows up.

In other words - if I overwrite the value in the column manually, it apparently gets rid of the unwanted apostrophe, and the formula works.

Question - why is the apostrophe showing up in the first place, and how can I easily rid myself of it so this formula works as expected? It must have something to do with the copy/paste special command I performed, but I've tried re-doing that in different ways, and still get the problematic apostrophe.

Thoughts?

Thanks.

SS Image-2.jpg

Find more posts tagged with

Formulas

Comments

ron.judenberg112096

It sounds like the data you pasted was treated as text and not numeric

Try this:

When you use the LEFT function to pull everything appended with "KB" from a column, add the VALUE Function as well.

So use =VALUE(LEFT(data,LEN(DATA)-2)) before your copy/paste

khankoff

Thanks, Ron.

I'll have to do some study of the VALUE function, as your suggestion worked to correct the problem. However, I've no idea how :-).

Still - a solution is a solution, no?

Kam Pierce

This apostrophe issue occurs when I import an Excel sheet into Smartsheet, even when the original format is numeric (not text). I could not figure out a way to avoid this issue and ended up having to manually delete the apostrophe so that an INDEX & MATCH formula could be used for this value. Extremely time-consuming solution since there were 550 rows in this sheet. I'm very invested in learning about a better solution, perhaps at the import step of the process?

Aaron Dyess

We are experiencing the same issue via an API integration. Did you ever resolve this other than manual? We did discover if you change the entire column to "Contact list" and then back to "Text/Number" the preceding ' is removed and the numbers are just that, numbers.

While this won't work for us - on a single import, it may help..

Sign in to join the conversation!

Quick Links

Trending discussions

How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
count if value is between a date range (reference between 2 sheets)
Hi team i have 2 sheets for material inventory. the first is to record witch equipment is ship out for repair and the other is the total of equipment available. the first is set as follow: equipment type, date out, date return, quantity (negative number) the other one is as follow: each row is for a single date with the…