how to separate date from specific word

[sharadmaxwell]

i have a data like below

327047534-DD-22-4-16
327199250-DD-2-5-16
327199250-DD-2-5-16
PRI-11458-DD-16-4-16
PRI-11478-DD-21-4-16
PRI-11565-DD-27-4-16
PRI-11565-DD-27-4-16
PRI-11565-DD-27-4-16
PRI-11630-DD-2-5-16

how to separate the date after "DD-"

attached here with sample file

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Kevin#]

Thank you for amending the title - it is much better when anyone else is trying to search for the same or similar subject.

one formula that will work (in adjacent cell in row2 and copy down)

Please Login or Register  to view this content.

formatted as follows

Format Cells.jpg

this is how it is built up

formula to extract text.jpg

[Kevin#]

If the data provided is typical you may get away with

Please Login or Register  to view this content.

If it is always 13 characters before "-DD-"

[zbor]

This will return last string after DD- regardles number of characters:

Please Login or Register  to view this content.

This will return date value:

Please Login or Register  to view this content.

[Tony Valko]

Maybe this...

=--MID(A1,FIND("DD-",A1)+3,10)

Format as Date

[sharadmaxwell]

=TRIM(RIGHT(SUBSTITUTE(A1,"DD-",REPT(" ",255)),255))
above formula is invisible in cell
but i want
=TEXT(DATEVALUE(TRIM(RIGHT(SUBSTITUTE(A1,"DD-",REPT(" ",255)),255))),"dd.mm.yyyy")
above forumla as invisible in cell
see the attached sample file

[shukla.ankur281190]

Try

C2=IFERROR(TRIM(RIGHT(SUBSTITUTE(A2,"DD-",REPT(" ",255)),255))+0,"") and the format the cells in date format.

[samba_ravi]

try

Please Login or Register  to view this content.

[sharadmaxwell]

Thank you very much experts
Thanks a Lot
it solve now

[Tony Valko]

I'm curious...

What is the regional date format for India?

[zbor]

According to wiki: https://en.wikipedia.org/wiki/Date_format_by_country

In India, the DD-MM-YY is the predominant short form of the numeric date usage. Almost all government documents need to be filled up in the DD-MM-YYYY format. An example of DD-MM-YYYY usage is the passport application form.[78][79][80] Though not yet a common practice, the BIS (Bureau of Indian Standards) of the Government of India introduced the standard named "IS 7900:2001 (Revised in 2006) Data Elements And Interchange Formats - Information Interchange - Representation Of Dates And Times" which officially recommends use of the date format YYYYMMDD, for example, 20130910 or 2013 09 10, or 2013-09-10 for the date 10 September 2013

[Tony Valko]

Thanks for that.

[zbor]

Er body but ya USA, er body but ya