1

I have an Excel file storing a thousand lines of dates. Each date seems to be (auto)formatted as a Date. A (PHP Excel) parser I'm using (really can't update/use another one) is parsing this to a string which will occur in the number of days till 1900.

Is there a way to format the values in Excel being simple text "08.03.1991" to get this file parsed correctly?

I could add a quote: "'08.03.1991" but I need an (Excel-based) one-action-solution for all the thousand lines.

Remark: Since this is a file of a user I can't just write simple VBA-Script or so to handle this since there will be new files in the future and the User needs to be able to solve this alone.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Jonathan
  • 1,955
  • 5
  • 30
  • 50
  • See [this answer](http://stackoverflow.com/questions/2636157/excel-date-to-string-conversion) –  Aug 13 '14 at 09:17
  • Sorry i dont see any solution for applying that to multiple rows at ones. (Without a VBA-Script) – Jonathan Aug 13 '14 at 09:22
  • 1
    Insert a new column, write the formula at the top, copy to the whole column. Is it manageable by your user? There is also the dumb solution: copy the column to Notepad++, use its find/replace feature to add a quote at the beginning of each line, and copy back to Excel, but I prefer the first one. –  Aug 13 '14 at 09:24
  • Or, to keep things as simple as possible: copy the whole column to any text editor (notepad will be enough), copy back to Excel, but use the Import Wizard to tell you want data as "text", not as "standard" –  Aug 13 '14 at 09:32
  • Excel will still autoformat this as date, whether I choose "text" or "unicode/text" – Jonathan Aug 13 '14 at 09:35
  • My Excel (2010) doesn't. When I check with the [CELL](http://office.microsoft.com/en-001/excel-help/cell-function-HP010062392.aspx) function (for "type"), it tells me it's a string. –  Aug 13 '14 at 09:37
  • To reproduce my problem: open a new excel file and fill in some dates like "08.03.1991". Excel now will automatically format this as "date". If I need this as a string I right-click on the cell to format it and choose "Text" or "Default" which does occur in "33305". If I write a new excel its easy if I knew this before to keep it as string, now the problem is there's already an excel file with 1000 lines formatted as date. – Jonathan Aug 13 '14 at 09:42
  • 1
    Use the **Convert Text to Columns Wizard**. See for example [here](http://office.microsoft.com/en-us/excel-help/trois-manieres-de-convertir-des-nombres-en-texte-HA001136619.aspx). –  Aug 13 '14 at 09:45
  • Great! This did the job. – Jonathan Aug 13 '14 at 09:46
  • Then you should accept pnuts's answer, as it's exactly his answer :-) –  Aug 13 '14 at 09:47

2 Answers2

3

I admit I am not quite sure what you have and what you want but it may be worth trying: Select column of dates, apply Text to Columns with Tab as delimiter and in step 3 of 3 select Text.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Formatting the cells as Text does occur in "33306" e.g. instead of "09.03.1991" – Jonathan Aug 13 '14 at 09:36
  • 1
    Yes! The same Wizard as copy to notepad and back, but without the need to copy. Nice trick :-) At least with my Excel, it works like a charm. –  Aug 13 '14 at 09:41
1

You could use the TEXT function like this:

=TEXT(A1,"dd.mm.yyyy")

For more details have a look here

Alexandru Cimpanu
  • 1,029
  • 2
  • 14
  • 38
  • But this would require me to edit ALL lines separately, right? – Jonathan Aug 13 '14 at 09:22
  • @xcy7e Not really separately. If a column has dates, you write the formula once and you copy it in only one copy/paste, or autofill. –  Aug 13 '14 at 09:27
  • you just click in the right corner of the cell in which you put the formula and drag down and it will autofill – Alexandru Cimpanu Aug 13 '14 at 09:27
  • I need to replace the values not add a second column containing the data of the previous column to be reformatted. I really need to replace the messed up excel date-format with a simple string. Is this not possible with Excel? – Jonathan Aug 13 '14 at 09:32