# Thread: Need help extracting a date from text.

1. ## Need help extracting a date from text.

Can someone provide a formula that will extract the date out of the following. I need it to be in a date format as I need to perform calculations with it.

CHPPRCHG20140824
CHPPRCHG20140824
CHPDAILY20140826
CHPDAILY20140827

Thanks!

2. =datevalue(mid(i2,13,2) &"-"& right(i2,2) & "-" & mid(i2,9,4))

3. Sunshine,

If you want to do it via Formulas Only:

Assuming data in col A:

In B1: =RIGHT(\$A1,8)
In C1: =DATE(LEFT(\$B1,4),MID(\$B1,5,2),RIGHT(\$B1,2))
Highlight B1:C1 Drag down.

If you prefer a User Defined Function solution:
Code:
```Option Explicit

Function ExtractDate(rngSource As Range) As Date

'Calling Sequence: =ExtractDate(A1)

Dim zDateStr  As String

zDateStr = Right(rngSource.Value, 8)
ExtractDate = DateValue(Left(zDateStr, 4) & "/" & Mid(zDateStr, 5, 2) & "/" & Right(zDateStr, 2))

End Function```
See col D for example of above code usage.
ExtractDates.JPG

Test File: Excel Extract Date UDF and Formulas.xlsm
HTH

4. Or
=DATE(MID(I2,9,4),MID(I2,13,2),RIGHT(I2,2))

5. Thanks so much!!!

6. RG's approach it is very versatile since the date on the right side has a stable number of characters (8 numbers) and allows any number of characters to its left. I am sure RG added the intermediate column (B) for better understanding of the formula but here is what it would look like if he electively condensed it.
=DATE(LEFT(RIGHT(A1,8),4),MID(RIGHT(A1,8),5,2),RIG HT(RIGHT(A1,8),2))

The coded =DATE(MID(I2,9,4),MID(I2,13,2),RIGHT(I2,2)) will fail if any characters are added or removed from the original first 8 letters making it more error prone.
E.g.
Added "Z": CHPPRZCHG20140824 yields the error #Value!
Removed "R": CHPPCHG20140824 yields #Value!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•