CODEWITHSUNDEEP
dateexcel-formula
WayneP
WayneP

Reputation: 3

extracting date from from string

I have following string:

CO_CAS_REA_NO_VIPPLUS_20190402_BONUS

I would like to Extract the Date part (20190402) and convert it into DD/MM/YY format if possible.

I have tried some 'Search' and 'extraction' formulas but to no avail.

does anyone have any ideas how to do this in excel or VBA?

Other variations of the data string would be like:

COCASREAHVPLUSVIPUK20190827CRMFSDEP
COCASREALVWINFIDEGL20190809CRMPTS
COSBINFLVFIDE20190830CRMBET
CO_CAS_RET_HVMV_UK_20190830_RB
COSB_REA_181INF_HVMV_FIDE_20190809_CRM_RB
COSBREAHVMVGL20190831CRMFBFSQUAL
COSBINFLVNO20190816CRMFB

Upvotes: 0

Views: 514

Answers (5)

bosco_yip
bosco_yip

Reputation: 3802

Assume your data put in Column A

In B2, enter formula :

=0+TEXT(AGGREGATE(14,6,--MID(A1,ROW($1:$250),8),1),"0000-00-00")

Then, B2 >> Format Cell, in the Number tab, choose: "Date" >> select dd/mm/yy

All copied down

Upvotes: 0

Terry W
Terry W

Reputation: 3257

Suppose in each string you only have one 8-digit numerical string representing the date in the form of YYYYMMDD that you want to extract, you can use one of the following three formulas to return the date:

Example

Method 1:

=DATE(LEFT(MAX(IFERROR(--MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),8),0)),4),MID(MAX(IFERROR(--MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),8),0)),5,2),RIGHT(MAX(IFERROR(--MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),8),0)),2))

Method 2:

=DATEVALUE(LEFT(MAX(IFERROR(--MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),8),0)),4)&"/"&MID(MAX(IFERROR(--MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),8),0)),5,2)&"/"&RIGHT(MAX(IFERROR(--MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),8),0)),2))

Method 3:

=DATEVALUE(TEXT(MAX(IFERROR(--MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),8),0)),"0000-00-00"))

Please note, all methods are using array formula so you MUST press Ctrl+Shift+Enter upon finishing the formula in the formula bar otherwise they will not function correctly. Then you can simply drag the formula down to apply across.

The logic is to use this array formula =MAX(IFERROR(--MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),8),0)) to extract the numerical string first. Method 1 & 2 follow the same logic which is to extract the year YYYY, month MM and day DD component from the numerical string and then use use DATE or DATEVALUE function to return the date. Method 3 is using a formatting trick to quickly turn the numerical value into a text date and then use DATEVALUE function to convert the text date into a "real" date.

Let me know if you have any questions. Cheers :)

Upvotes: 1

LafaMan
LafaMan

Reputation: 180

Assuming the strings are in Column A and you're only looking for the year 2019 you could use this

=DATE(LEFT(MID(A1,SEARCH(2019,A1),8),4), MID(MID(A1,SEARCH(2019,A1),8),5,2), RIGHT(MID(A1,SEARCH(2019,A1),8),2))

Upvotes: 0

Solar Mike
Solar Mike

Reputation: 8375

You might find this more flexible if the text to the left changes:

enter image description here

Just to save you typing here is the formula in cell C1:

=MID(A1,FIND("LUS_",A1,1)+4,FIND("_BON",A1,1)-FIND("LUS_",A1,1)-4)

It does however assume that the 4 characters either side of the date are always the same.

Upvotes: 0

henrywongkk
henrywongkk

Reputation: 1918

with excel-formula, make use of MID() function

=MID("CO_CAS_REA_NO_VIPPLUS_20190402_BONUS",23, 8)

with VBA, you can split it with "_"

dte_str = Split("CO_CAS_REA_NO_VIPPLUS_20190402_BONUS", "_")(5)

Upvotes: 0

Related Questions