Removing noise information and converting to date format

[nishanth.nottath]

Dear experts - I have a file with date and other gibberish added in each line as below. I need a formula that can:


1)Remove all characters after the date (this could be 18 - 20 characters)

2)Convert the remaining date piece into readable date format


2012-1-10.22.10. 38. 154000000

2012-1-11.13.37. 45. 35000000

2012-1-12.19.2. 59. 334000000

2012-1-20.22.26. 34. 151000000

2012-1-29.11.12. 21. 777000000

2012-1-31.17.56. 36. 353000000

2012-1-6.18.46. 57. 997000000

2012-2-10.11.12. 42. 326000000

2012-2-10.18.45. 36. 241000000

 

[NARAYANK991]

Hi Nishanth ,


You need to confirm what are the readable dates that you want to obtain from the above data.


Suppose we consider the first line of data :


2012-1-10.22.10. 38. 154000000


The relevant characters for the date are only the left-most 9 characters , unless you want to include the 22.10 also. Either way , stripping off 19 characters will not retain the 22.10 ; removing only 15 characters will.


Can you clarify ?


You also need to specify the date format for the output ; is it to be mm/dd/yyyy , dd/mm/yyyy , dd-mm-yyyy , dd.mm.yyyy , or any of the other possible formats.


Narayan

 

[oldchippy]

Hi nishanth.nottath,


Welcome to the forum, assuming the date is before the first full-stop, then


=DATEVALUE(LEFT(SUBSTITUTE(A1,"-","/"),FIND(".",A1)-1)) format the cell to DATE

 

[nishanth.nottath]

Dear Narayan and oldchippy - thanks very much for the quick feedback. Oldchippy - that was brilliant and it worked!


Thanks for the welcome to the forum - I'd been reading through for a while, but this is my first post. This was very helpful and I hope to continue using it.

 

[oldchippy]

Hi nishanth.nottath,


Thanks for the feedback, glad it helped