Unwanted formatting of time data

[Lucas]

Hello. I have imported some data into Excel. One of the columns represents
time and looks like this: "2008-02-14 15:40:07,734". I do a find-replace to
replace all ',' in this field with '.'. Whenever I do this, however, Excel
converts all of the data in this field to some sort of time representation.
This is not what I want. I just want the original text as it was.

I have tried setting the field to General and Text formats, and Excel still
does the strange time formatting after Find/Replace. After the conversion,
I've tried setting the field to Text, and I just get some silly time
representation that looks like this: "39492.6528672917"

Does anyone know how to turn off this autoformatting? I've looked all over
hte options and can't find anything.

Thanks,
Lucas

 

[David Biddulph]

=TEXT(A2,"yyyy-mm-dd hh:mm:ss.000") will convert your time to text.

Another option is to use =SUBSTITUTE(A1,",",".") on your original cell
(instead of doing Edit/ Replace), and that will leave the result as text.

 

[Lucas]

Thanks, David! That worked fine.