MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formatting numbers at text import


Posted by Safa Egilmez on December 07, 2001 12:46 PM

I hope you can help.

I use excel to manipulate data that I export from an ORACLE database in text file (unfortunately this is the only way to export so far). I open the text file in excel and do the necessary manipulations. I have a column where the date and time is combined together as one (ex. 1/23/2001 937) [Which should be formatted to 01/23/2001 and 0937 hours (if possible in a seperate column), I use military time]. When I import this to excel, it only comes as "1/23/2001 937" . I tried to format this and copy and paste the values to another cell but it does not work. Is there a way to format them and also seperate the time into a different column? Thanks.


Posted by Mark W. on December 07, 2001 1:11 PM

With your text representation of a datevalue in
cell A1...

1. Use =LEFT(A1,FIND(" ",A1))+0 for the date portion
2. Use =TRUNC(RIGHT(A1,LEN(A1)-FIND(" ",A1))/100)/24+RIGHT(A1,2)/1440
for the time portion

Posted by safa egilmez on December 07, 2001 2:36 PM

Thanks,

I will try it.