#INVALID OPERATION - Apostrophe catastrophe

khankoff

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.

 

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