Issues with Importing and/or Copy/Pasting in Numbers

Nancy Heater

Hello.

I am having a bit of a challenge with bringing numbers into smartsheet, and its an issue that isn't constant across all of my sheets which is what has me stumped.

I need to copy/past in numbers each week to (2) different sheets from (2) different excel sources.

When I copy/paste in from excel sheet #1, the numbers populate in as shown below, with an apostrophe in front of the number (which you can only see when you double click on the cell) which breaks my formulas. When I copy/paste in from the excel sheet #2, everything shows up just fine.

How can I remove the apostrophe in front of the numbers when I'm copy/pasting them in? I've tried changing the data in excel to Text, General, Number, etc and nothing seems to make a difference. Right now, I am having to go in and manually delete the apostrophe which isn't sustainable.

Thank you in advance for the assistance!

Dale Murphy

Nancy;

Have you tried the other combinations, namely Excel 1 to SS 2 and Excel 2 to SS 1? It would be interesting to see the full pattern.

In my experience importing data from Excel, the issue has been with the data in Excel (Well, unless I've done something silly like dropped text into a Date column.)

I recommend building a column in Excel and forcing your data to be real data, not text. VALUE() might clean things up?

A more manual intervention would be to do a search and replace of ' using CTRL-F.

On the other hand, SS is a bit quirky when it comes to apostrophes. Try using LEFT() on that leading ' to see what I mean.

Cheers,

Dale

Nancy Heater

Thanks @DMurphy. We've tried a variety of different imports and copy/past techniques and are still unable to get the data to reflect the same from different sources.

I have yet to be able to find a way to get rid of the apostrophe, but I'll keep trying.

Thanks again.

Mark Cronk

Hi @Nancy Heater ,

@DMurphy is on the right track. After you import into Smartsheets, add a text column with the formula =Value( [insert cell with ']). Make this a column formula or copy it down. Then use this value in your formulas.

Excel formatted as numbers should work on import though.

Mark

Sofia Fernandez

@Nancy Heater, I'm with you on this one.

I find this problem exists even when you are "pasting" values from a cell with a function in it. It seems like a very unintuitive and cumbersome step to have to remove a tick mark.. I think this is a real bug; if someone could explain to me the advantage, I might change my mind.. but it really doesn't make sense that this character is added.

I didn't import or export any data from MS anything - the apostrophe also gets added from within smartsheets and its functions.😩

Diego Treviño

Hi @Nancy Heater @Sofia Fernandez ,

I had this same issue with a colleague (whenever I pasted values everything worked fine but when she did the apostrophe appeared) and we managed to solve it by making sure we both were under the same Regional Preference in our personal configurations. I assume that based on your regional preference certain formats are automatically applied.

Hope this helps.

Steven Gearhart

This happens to me almost at Random when copying rows from one smartsheet to another. Super annoying to find out when my "Duplicate" number check formula stopped working and I sent people additional requests they have already received because of the hidden ' apostrophe. Had to check 100's of rows 1 x 1 to clean it up, what a pain. I too think it is a Bug in system.

Steven Gearhart

@DMurphy FYI, the "Ctrl-F" function will not find the hidden ' in front of numbers. I tried that first. It doesn't recognize them. So that manual method of finding and replacing with a blank spot will not work. Sorry.

Jacob Minnich

Just found that I'm having the same problem, even when entering values into the Primary Column by hand. After I enter a value in the cell, Smartsheet adds an apostrophe before the value I entered. I'm trying to use a duplicate finding formula in another column, but it's useless as long as this issue persists.

Edit: it appears this only happens when I enter a value that Smartsheet thinks is text. Entering a number doesn't cause this problem, but entering a number preceded by a zero will insert an apostrophe before the entry.

Stultze

I was having this same issue. We did a VLookup function referencing another Smartsheet to import costs to a tracking sheet. The source data was mostly numbers with a few notes in some of the cells. Many of the cells had the apostrophe before the number after we pasted the values in place of the formula using 'paste special'.

I was able to get rid of the apostrophes by changing the column properties from 'text/number' to 'date' (without checking the 'restrict to dates only' box) and then hitting 'OK'. I then went back into the column properties and changed it back to 'text/number' and it had removed the apostrophe from all cells in the column. Unfortunately, about 20 of the 900 cells of data were permanently converted to dates and had to be manually updated.

Sander K

I have the same issue. But not with copying/pasting. Some of my form inputs come with a hidden apostrophe, that can only be seen when double clicked on the cell. Meaning that no functions that use the values from that cell works. Form has validation, so only numbers can be inserted. Approximately 30-40% of the form inputs are with and apostrophe.