Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to prevent Excel converting imported fractions into dates
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to prevent Excel converting imported fractions into dates
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to prevent Excel converting imported fractions into dates
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. -- CaptainQuattro ------------------------------------------------------------------------ CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763 View this thread: http://www.excelforum.com/showthread...hreadid=526517 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to prevent Excel converting imported fractions into dates
"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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to prevent Excel converting imported fractions into dates
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to prevent Excel converting imported fractions into dates
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. -- CaptainQuattro ------------------------------------------------------------------------ CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763 View this thread: http://www.excelforum.com/showthread...hreadid=526517 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort order : Excel vs imported data | Excel Discussion (Misc queries) | |||
Dates and Recomended Graphing Programs to work with excel? | Charts and Charting in Excel | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
fractions turn into dates when pasting in excel 2003 | Excel Discussion (Misc queries) |