johnstrohecker
Newbie
Joined: 15 Sep 2007
Posts: 1
|
 Posted: Sat Sep 15, 2007 8:27 am Post subject: Any way to prevent Calc from autoformatting web data? |
 |
|
I am building a spreadsheet that requires me to import data from tables located on several websites.
One of these tables contains data formatted like this:
0-5
2-11
etc...
I cannot get calc to treat this imported data as text and as a result each of these entries is reformatted as a date...which is exactly what I do not want to happen.
I have tried setting the format on all cells in the appropriate range as text and then refreshing the external data, but Calc still autoformats the resulting information.
I have also tried unchecking autoinput under the cell contents option...this also has no result.
I am wondering if this is possibly a bug that hasn't been ironed out by the development team.
Any information appreciated.
Thanks,
John |
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sat Sep 15, 2007 11:09 am Post subject: |
|
|
I don't consider this as a bug in the sence that the software behaves differently as designed. It seems that this feature is not well designed. It does not fill the table with contents only. It overrides all formatting as well. I tried to re-import with number format "Text" in the respective columns, which would be the feature to prevent type-guessing for all newly created content. But all formatting is overridden on import.
If it could keep the formatting (like in datasource import) or if it would provide an option to set the type of columns (like in csv import) we could handle this.
"1-12" gets imported as a number, formatted to represent "1st of December" or "December, 1st", depending on your input locale beeing English(US) or not.
Quick work-around (no it's a work-after):
The following formula should restore the XX-YY string from date, assuming that there are always pairs of numbers separated by "-".
| Code: | | =IF(ISTEXT(B2);B2;IF(YEAR(B2)=YEAR(TODAY());DAY(B2)&"-"&MONTH(B2);MONTH(B2)&"-"&RIGHT(YEAR(B2);2))) |
I tested with the following table and British locale.
Column A, formatted as text, showing what I have typed into B.
Column C contains the formula.
| Code: |
Input Result Restore
31-12 31/12/07 31-12
12-31 01/12/31 12-31
99-12 99-12 99-12
12-99 01/12/99 12-99
1-12 01/12/07 1-12
12-7 12/07/07 12-7
|
Test carefully with all possible combinations of XX-YY
If it works and you refresh the import, just copy the conversion column and paste special (strings without formulas) over the dates. |
|
