Disable Date Reconigtion

[Steveo59]

The only way i can import the data i need is copy and paste. The trouble is that when i do this column E is in fractions i.e 5/6 and this is always shown as a date. Is there anyway to disable this? I know you can disable date reconigtion if importing data from the web but is this possible using copy/paste ? please help as this is driving me nuts. Im using excel 2007.

 

[xenou]

You'll need to format the sheet (or more likely just the relevant columns/cells) as Fraction before pasting in the data:

Format | Cells and choose fraction under the Number tab for formats.

AB

 

[Steveo59]

Alexander Barnes thanks for the reply but it doesn't seem to work

 

[xenou]

1) Where are you copying and pasting from? Another Excel sheet?

2) Since you describe this as importing data, is the data coming initially from another source besides Excel?


Regards

 

[RoryA]

When you paste it do you get the Paste Options smart tag appearing? If so step through the text import wizard and specify a Text format.

 

[Steveo59]

The Data is imported from email, and i dont see the paste options!

 

[DonkeyOte]

If there are no options before pasting and formatting the cells prior to import doesn't work you could, if the import forces a recalculation of the sheet, you could try a VBA solution (that you wouldn't necessarily see take place)

Right click on the sheet tab name in to which the data is imported and select View Code, paste into that sheet the below:

Private Sub Worksheet_Calculate()
Columns(3).NumberFormat = "0.00000"
End Sub

Change 3 (Column C) to be whichever column is affected and the number format to match your own criteria.

If the import doesn't invoke a calculation you could think about adding a dummy cell that links to the first cell that will be populated with a decimal using something like 1*cellref --> this should then fire the calculation event and format the column automatically - ie before you really see it.

I hope that helps.

 

[Steveo59]

thanks for the replies, but i still cant get it to work. thanks anyway.

 

[DonkeyOte]

could you post up some samples (post import pre-reformat) and also specifiy where they reside in terms of column reference etc...

also - what exactly is it that you are importing from email and how are you importing data ?

 

[DonkeyOte]

I have seen the data and can confirm that the data comes from an external website which when imported as text/unicode/html will always change the values to a date (losing the "original" text string -- not a number)

To get around this:

Prior to importing set Column D format to be Custom: d-m
Prior to importing set Column E format to be Custom: d/m