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

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]

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


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]

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]

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.