Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

When i copy data from a site and paste in excel numbers are converting to date format EXAMPLE: 2-1 converting to 1-feb how to stop this. i have so many records so cant do manually. is there any quick process?

share|improve this question

migrated from stackoverflow.com Jun 28 '11 at 7:16

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

1  
Select the empty fields (or the entire column), set them to a string format instead of the default value, and do a special paste (available on right-click) with "values only" to prevent a date format from being auto-applies... –  Victor Nicollet Jun 28 '11 at 7:06
1  
@Victor: Please repost your comment as an answer (that's what it is!) so we can upvote it and the asker can "accept" it. –  Dave Sherohman Jun 28 '11 at 9:16

5 Answers 5

Select the empty fields (or the entire column), set them to a string format instead of the default value, and do a special paste (available on right-click) with "values only" to prevent a date format from being auto-applied.

(Moving Victor's comment to an answer, as per MSO, as OP and Victor haven't done so in a week)

share|improve this answer

There are few ways to resolve that, the most common and simple one would be to format the cells prior pasting the data as text (select range, right click, format cells, double click on text).

What I have done was to simply change the decimal separator:

File -> options -> advanced -> untick "use system separators" and set the decimal separator to a dot "." (default in my case was the comma).

Check http://cloudishvps.com/windows/stop-excel-2013-auto-converting-numeric-values-dates/ for screenshots if needed.

share|improve this answer

The problem you have is that the field or the whole column is set to contain the data in a DATE FORMAT. To overcome this, a simple way is to select the field or the entire column, 'Right click' > Format Cells > Click Number (Select sub-options according to your requirement) > Press OK.

Hope this helps.

share|improve this answer

Before pasting, select column that will hold the non-date value. Right click. Select 'format cells'. Then select 'text' as the format and click OK.

Now, go ahead and paste, but paste using the 'match the destination formatting' option.

share|improve this answer
  1. Paste data
  2. Set problematic cell type to text
  3. Paste data over again without deleting
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.