up vote 1 down vote favorite
share [g+] share [fb]

I am copying tabular data from a website: [http://www.infodriveindia.com/TradeResources/STDCode.aspx?STDCode=&City=&State=andaman][1]

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.

link|improve this question
57% accept rate
2  
Pasting data into Excel? Shouldn't this be on superuser.com? – Dan McGrath Feb 14 '10 at 18:09
oh sorry, i dont know – Rajasekar Feb 14 '10 at 18:11
Duplicate of previous post: stackoverflow.com/questions/2261787 – Paul R Feb 14 '10 at 18:14
feedback

migrated from stackoverflow.com Feb 14 '10 at 19:09

This question came from our site for professional and enthusiast programmers.

4 Answers

up vote 2 down vote accepted

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.

link|improve this answer
i changed that, still it truncates value. May be the reason is the data i copied is inside a table – Rajasekar Feb 14 '10 at 18:10
It works when i enter data manually. But when i paste it, it didnt working – Rajasekar Feb 14 '10 at 18:13
But once copy-pasted that will automatically convert it into number type, he then needs to prep-end the values with "0" somehow. – Manish Feb 14 '10 at 18:14
-1: Edited but still not a proper answer! – Manish Feb 14 '10 at 18:17
1  
I did, that is why I was stating in my case it would be 2, 4 and 5. Remeber to use Paste Special, As Text, and chnage the Column Type from General to Text – astander Feb 14 '10 at 18:27
show 1 more comment
feedback
up vote 1 down vote

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

link|improve this answer
it means what??? plz help, im very beginner in excel – Rajasekar Feb 14 '10 at 18:09
But once copy-pasted that will automatically convert it into number type, he then needs to prep-end the values with "0" somehow. – Manish Feb 14 '10 at 18:15
feedback
up vote 1 down vote

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

link|improve this answer
feedback
up vote 1 down vote

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.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.