Extracting date from a series of numbers

Solved/Closed
Report
Posts
28
Registration date
Wednesday January 27, 2010
Status
Member
Last seen
May 27, 2013
-
 Santos -
Hello,

I need to extract "date" exsiting in a specific space in a series of numbers and get it in another cell. I don't want to split the series using 'text to column" and then pick up the date.

Example:

Data (Column A)
20100103001
20100203555
20100304668
20100201365
20100201568

Where: space 1 to 4 indicate year, space 5 to 6 indicate month, space 7 to 8 indicate date and space 9 to 12 is any sequence number and doesn't needed in Answer column B and is ignoreable.

The Required Column B should be as follow:

Column B
03-Jan-2010
03-Feb-2010
04-Mar-2010
01-Feb-2010
01-Feb-2010

Thanks......seekermay

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))
Format it to the way you want as this is a date to excel and not some thing that looks like a date
really nice to solve to prob.
Posts
28
Registration date
Wednesday January 27, 2010
Status
Member
Last seen
May 27, 2013
5
thanks! It works.