A Formula to Extract Dates and Times

[14eichenwalda]

Hello! Thank you for looking this over. I appreciate it.

I have a large number of files in the following format: 943291169-20100419_201417. That is, unimportantnumbers-date_time. I'd like to pull the date and time out of that name without having to go through the files one at a time and place them into new cells. I'd like to have one column of cells for date and one for time.

I asked a similar question a few months ago and got a fantastic response. However, I'm not sure how to alter the formulas I recieved to the new format (My older files were in the format Location_Date_Time [BUBL_20130808_194941_000])

The old formulas were =0+TEXT(MID(A1,FIND("_",A1)+1,8),"0000-00-00") for the date and =0+TEXT(MID(A1,LEN(A1)-9,6),"00\:00\:00") for the time.

Thanks again! I look forward to your response.
[TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

Same principle, but simpler:

=TEXT(MID(A1,FIND("-",A1)+1,8),"0000-00-00")+0

=TEXT(RIGHT(A1,6),"00\:00\:00")+0

 

[14eichenwalda]

Thank you very much! That's exactly what I needed