How to prevent Excel converting imported fractions into dates

[Phil A in the UK]

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.

[Ron Rosenfeld]

On Sun, 26 Mar 2006 15:05:35 -0800, Phil A in the UK <Phil A in the
[email protected]> 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?
--ron

[CaptainQuattro]

One way to change the way Excel will treat this data is to select Tools, Options, Transition.. and select Transition formula entry.

If you are simply copying your table to the clipboard and pasting into excel, they will display as decimals, but the underlying fractions will be preserved.

Another method would be to perform an interim step of copying your table to a notepad plain text document. Save the notepad table as something like table.txt and then use the

Data, get external data... wizard in excel. (In Excel 2003 this function is named slightly differently, but is still under the Data Menu)

Original Data type will be delimited
Click Next. Choose Tab as the delimiter
Click Next and for each column choose Text as the Column Data format.

Hope this helps.

[Phil A in the UK]

"Ron Rosenfeld" wrote:

> On Sun, 26 Mar 2006 15:05:35 -0800, Phil A in the UK <Phil A in the
> [email protected]> 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.

[Phil A in the UK]

"CaptainQuattro" wrote:

>
> One way to change the way Excel will treat this data is to select Tools,
> Options, Transition.. and select Transition formula entry.
>
> If you are simply copying your table to the clipboard and pasting into
> excel, they will display as decimals, but the underlying fractions will
> be preserved.

Yes, I am - see reply to ron. Thank you Captain Quatro, that method worked
fine, but only if I closed excel and re-opened a new sheet before setting
that option. Just changing the option and pasting into an empty 'sheet2' gave
the same error as before.

> Another method would be to perform an interim step of copying your
> table to a notepad plain text document. Save the notepad table as
> something like table.txt and then use the
>
> Data, get external data... wizard in excel. (In Excel 2003 this
> function is named slightly differently, but is still under the Data
> Menu)
>
> Original Data type will be delimited
> Click Next. Choose Tab as the delimiter
> Click Next and for each column choose Text as the Column Data format.

Saving that web page in notepad produced space delimited columns, not tabs,
which is awkward as there are spaces already in the text, eg ' 1 1/4'
(=1.25), and '# 38'. Instead I pasted it into Word, converted the table to
text with tab delimiters, saved and then imported as per your method.

This resulted in a sheet that 'looked' the same as the web page data, but
was of course all text format. eg '1/16' was text not '0.0625' in the cell.
Hitting return in the cell converted this to '01/01/2016' which is where we
came in :-(.
I do want to do numerical work on the data so can't leave it in text format.

Your first method is the best solution for me, though I would like to make
the 'Transition formula entry' stick as the default option. At the moment it
seems to revert to unchecked for each new sheet.

Thanks very much for the workaround. Is there any way to get microsoft to
fix this wierd parsing as it does seem to be a bug to me?

> Hope this helps.

Yes it did thank you.

Phil

[Ron Rosenfeld]

On Mon, 27 Mar 2006 02:07:02 -0800, Phil A in the UK
<[email protected]> wrote:

>
>
>"Ron Rosenfeld" wrote:
>
>> On Sun, 26 Mar 2006 15:05:35 -0800, Phil A in the UK <Phil A in the
>> [email protected]> 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

[CaptainQuattro]

If you want Excel to default to Transition formula entry at startup, you can save a blank workbook with this option set, in the XLStart directory:

Root\OS\Profiles\User_name\Application Data\Microsoft\Excel\XLStart

(e.g. C:\Windows\Profiles\Phil\Application Data\Microsoft\Excel\XLStart). You can also set another directory to be the startup directory. This is done under Tools, Options, General, Alternate startup file location. Just be careful to create a brand new directory for this purpose, because all files in the startup directory are automatically loaded.

You can also save a blank workbook with the option selected, as a template. Name the template something like Transition.xlt. Then when you click File, New... you will have the choice to create the new workbook with or without transition formula entry turned on.