Deconcatenate date

[Pallando_II]

I downloaded a set of transactions and the date ended up in two similar text formats, e.g.:

JAN12017 (8 char) and
JAN1020I7 (9 char).

I'd like the date to be reformatted into numeric, specifically

1-JAN-17 (or 2017) and
10-JAN-17 ".

i have >600 dates to reformat so I need a formula. Although I'm familiar with most aspects of Excel, nothing I've tried works completely. Any ideas? Thanks.


Pallando
San Diego

[shukla.ankur281190]

Assume you have data in A1 then Try

B1

Please Login or Register  to view this content.

Drag down!!

[FDibbins]

Another option...

	A	B
1	JAN12017	1/1/2017
2	JAN102017	1/10/2017

B1=DATEVALUE(MID(A1,4,IF(LEN(A1)=8,1,2))&LEFT(A1,3)&RIGHT(A1,4))
Note that in your 2nd example, you have 20I7 (letter I) not 2017 (number 1)

[FDibbins]

Assume you have data in A1 then Try

B1

Please Login or Register  to view this content.

Drag down!!

That is still text, not a date

[Pallando_II]

Keen "I" Mr. Dibbins. Was not intentional.

[Pallando_II]

Mr Ankur's formula conversion provided a numeric result for me, albeit the date's being in its 'basic' format (#days from 1900); then simple reformat.

Just saw he edited it per your comment, which is when I probably used it.

[FDibbins]

Happy to help, thanks for the feedback
(good pun there, too )

[leelnich]

Hi all- Ankur, I liked the way you coerced the text to date. Here's a slightly tighter version of your formula:
=--(MID(A1,4,LEN(A1)-7)&"-"&LEFT(A1,3)&"-"&RIGHT(A1,4))

And Ford recognized delimiters weren't required! Combining the best of both:
=--(MID(A1,4,LEN(A1)-7)&LEFT(A1,3)&RIGHT(A1,4))

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee