On Mon, 27 Mar 2006 02:07:02 -0800, Phil A in the UK
wrote:



"Ron Rosenfeld" wrote:

On Sun, 26 Mar 2006 15:05:35 -0800, Phil A in the UK <Phil A in the
wrote:

I am importing a table into Excel. The table is text and has columns which
include fractions such as 1/8, 3/16, 5/32, and also composite fractions such
as "1 1/8", "2 3/4" (but without the quotes).

Excel thinks these are dates and autoconverts them to a date serial number.
Once this happens there is no way to recover the original fractions.

I have tried preformatting the columns where the data will end up as
'Fraction' format, and 'Text' format but that makes no difference.

Is there a simple way of stopping this behaviour? At present I am manually
pre-processing the data in Word to put quote marks around the fractions so
they are imported into excel as text. I can then use formulas to re-create
the deired numerical fractions.

How are you importing the data?

Cut'n'paste from a web page such as this
http://www.britishmetrics.com/html/bswstd_1.htm where the fractions are
fractions of an inch. Excel incorrectly parses the fractions. Some are
treated as dates in 'this year', eg 1/8 becomes 1-aug-2006. Others as dates
in different year's, eg 5/32 becomes 1-may-1932. My international settings
are dd/mm/yyyy.

Yes, Excel is a PITA in this sort of situation.

Here's one possible solutions.

Before Pasting:
Select Tools/Options/Transition and select Transition Formula Entry.

After Pasting:
Format the "fraction columns" as fractions.
DEselect Transition Formula Entry.

You can probably record a macro to do this.




--ron