extract date from text

[mazan2010]

Hello all
i need to extract date from this value (21703301B) to become 30-03-2017
i found this formula but it extract 21703301

ALT+CTRL+Enter
will appreciate all help
regards

[AlKey]

Try this

Please Login or Register  to view this content.

I wasn't sure if there will be parentheses if yes, use this

Please Login or Register  to view this content.

[mazan2010]

Dear ALkey
many thanks its work good
please can modify in the following formula

that from Date give text as thw foloowing
27-12-2017 to give me 21712271C

[mazan2010]

Try this

Please Login or Register  to view this content.

I wasn't sure if there will be parentheses if yes, use this

Please Login or Register  to view this content.

please see my attahced file that
the last formual give me correct result with year 2017 and year 2016 it give me 2006 why ?
i marked it with yellow

[AlKey]

Try
Enter in F2 and copy down
Format m/d/yyyy

Please Login or Register  to view this content.

v	A	B	C	D	E	F
1	04/08/2016	201608041B	14,523	14,251		8/4/2016
2	07/08/2017	21708071B	17,335	17,310	21708071	8/7/2017

[mazan2010]

it work good many thanks for your effort
what about this
27-12-2017 to give me 21712271C
=IF(ISBLANK(G2),"",TEXT(G2,"yyyymmdd")&"1C")

[johnmpl]

Hi to all!

Like your examples...

1. If you have like 21703301B or 201703301B, and you want to extract date, you can use:
=--TEXT(LEFT(IF(LEN(B1)=9,REPLACE(B1,2,,0),B1),8),"0000-00-00")

Later put the format that you want.

2. If you have a date, like 27-12-2017, you can use
=REPLACE(TEXT(F1,"yyyymmdd"),2,1,)&"1C"

Blessings!