How to prevent truncation of 0 in Excel sheet?

I am copying tabular data from a website: [

But it pastes the numeric value by truncating the leading value. For example 03192 as 3192. But I want it to paste as it with the leading 0 (03192). Please help me to solve this problem.

Please try to copy and paste that tabular data in the above URL onto your Excel sheet and check.

3

4 Answers

Change the column type to Text

• Right Click the column in question.
• Select Format Cells...
• Select the Number tab
• Select Text

EDIT:

OK, try

• Copy data from site
• Right click A1 and choose past special.
• Select Text
• Select Column A and use Text to Columns
• Select Fixed width and click Next
• Remove unwanted breaks (in my case it would be 2, 4 and 5) and click Next
• Change Column 1 type from General to Text

That should do it.

6

Depends on your version of Excel as to how you get there, but the best thing to do is set a custom number format like

00000

for the affected cells. That way you can retain the data type - it remains a number inside Excel instead of becoming text, also should the results you get start delivering numbers beyond 99999 or decides to truncate them for you then you aren't relying on the underlying data.

Prefix it with ' (single quote). That's a signal to Excel "treat this value as text".

2

Instead of pasting, import the data (and specify that it is text) - import is found in the data tools.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy