How to remove the apostrophe in front of a number on data imported from Excel

MarianneD617

I have a column with Rank values on a sheet with data imported from Excel. The rank value includes an apostrophe in front of the number.

What is the specific formula I need to use based on the example below? I've been trying to play with the examples given and keep getting errors.

Thanks.

Brent Wilson

This has to do with a feature of importing

This will clean the data and have to be done once after importing

1. Create a column.. call it something like Cleaner
2. Put the following in the cell "=RIGHT([Global Rank]@row, LEN([Global Rank]@row) - 1". This will remove the '
3. Convert it to a Column Formula
4. Now all the values in that helper column will have the ' removed
5. Select All Values in that row and paste them back into the Global Rank as Values
6. Delete the helper column

MarianneD617

Thank you!

Brent Wilson

This has to do with a feature of importing

This will clean the data and have to be done once after importing

1. Create a column.. call it something like Cleaner
2. Put the following in the cell "=RIGHT([Global Rank]@row, LEN([Global Rank]@row) - 1". This will remove the '
3. Convert it to a Column Formula
4. Now all the values in that helper column will have the ' removed
5. Select All Values in that row and paste them back into the Global Rank as Values
6. Delete the helper column

MarianneD617

Thank you!

jshrum

This might not help, but thought I would share.

I had this same thing happen several months ago. Out of nowhere I was getting an apostrophe in front of numbers that start with zero. At the time, I was using Excel .csv files for my data shuttle automations. I switched to Excel .xlsx files and it fixed the problem.

I am building additional sheets and ran into the same problem again, but this time I was using the .xlsx version. I changed it over to .csv and it fixed the problem.

I did a test and deleted all the information on the sheet and ran the shuttle again using the .xlsx, and the apostrophe came back. Deleted and ran .csv and it works again.

What is also interesting is that on the excel sheet, for the column I'm referencing, it has a 0 in front, ie.. 07. When I switched to get rid of the apostrophe, it also got rid of the 0 in front, leaving just, 7, which is what I want anyways. Weird, I know.

In this situation, I have two smartsheets that are pretty much the same. I had one and created a copy for the other. For the original smartsheet, I am using .xlsx... for the copy, I am now using .csv.

No idea what is happening, but if your sheet will allow it, try using one or the other and see if it works.

CAS the CSA

=right({range},6) for a 6 digit number doesn't clear the apostrophe

Is there really no function to make numbers, that have never not been numbers, to paste as numbers?

CAS the CSA

=RIGHT([Global Rank]@row, LEN([Global Rank]@row) - 1 results in the right 5 characters of a 6 digit number. It doesn't resolve this apostrophe issue.