Preserve cell formatting

[Patrick_M76210]

I'm using an excel sheet to upload into another system. Different users are using these upload sheets and these sheets contain large data records (up to 100k). There is a column which represents the part number from the system. This column is formatted as 'text' and needs to preserve this formatting. When users edit these sheets (due to vast number of records) they copy the values from a different excel worksheet and paste into the target part number column. However, by doing this, it pastes the source formatting to the target. An example, when a user copies following value number formatted cell "536421554870000002357" it will automatically convert the target cell to number format and display "5.36422E+20" which fails during the upload procedure. How can I prevent this event?

[#2]

@Patrick_M76210 

This article explains why it happens. The best way to bypass this is to insert an apostrophe in front of numbers to convert it to a text (Source)

[#3]

@adversi 

Thx for the feedback. It places the apostrophe in front which is now seen by excel as a character. When importing into our target application, it now imports the apostrophe as a character into the target tables. Hopefully in the future this will be solved through a standard excel feature.

[#4]

@Patrick_M76210 

To remove single quote in front of numbers in a cell, you can highlight the entire column where the pasted text figure will be and Clear Formatting.

Go to Home Tab > Editing Group> Clear > Clear Formatting