11

When I copy data from a site and paste in Excel, some numbers / numeric strings are converting to date format.  For example: "2-1" converting to "1-Feb".  How can I prevent this?  I have too many records to do manually.  Is there any quick process?

2
  • 2
    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
  • 2
    @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
8

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)

2

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.

0

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.

0

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.

0
  1. Paste data
  2. Set problematic cell type to text
  3. Paste data over again without deleting
0

convert field with number format into float format in your query and paste the result. If amount is a number then, select cast (amount as float) as amount from table;

-1

1.) try to add this sing (') in you number in source data then past it into excel the replace (') with same (') because when you add this sing it will be shown in your data in excel but when replace it with it will be hidden.

2.) If your typing then simply add this sign (') before your number then excel will not convert it into date and this sign will not be shown in your

Your Answer

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