Formulat for Extract date after spacific words

[santosh226001]

hello Experts
Sessions Greetings

We have a excel file aprox 20500 rows data. we attach a sample file
in this file we want Extract Date (in indian form we etner dd/mm/yyyy and data is enter in this form but not in date type) in a new cell we want that Date which will come after "दिनांक ".
we Want only date after "दिनांक " First find ... in cell we have many time दिनांक word use...

we mention red color in sample file which date we want to extract in new cell..

Thanks in Advance

[Glenn Kennedy]

Try this, copied down:

=SUBSTITUTE(MID(TRIM(A1),SEARCH("दिनांक ",TRIM(A1))+7,10),".","/")+0

format cells as dd/mm/yyyy

[santosh226001]

sir glenn sir when ur forumal we copy in my file and copy down then some cell show date but some cell show value...

in ur attach file when we press f2 to show formula and then enter then cell value covert few place to #value! error..

is there change any Date type in formet cell???

[Glenn Kennedy]

Are the problem dates dd.mm.yyyy? Post a sample with problem dates.... otherwise I am left guessing.

[santosh226001]

yess sir its come probelm to dates come in dd.mm..yyyy case..
we attach screen shot in which is coming in sample file u have sent to me.. in my end

[Glenn Kennedy]

Check the regional settings on your PC. I suspect that your PC is set to USA location. It was all OK here for me.

[santosh226001]

yes my set to usa location.. now we change to uk.. and its works..

Thanks a lot sir we add reputation.. thanks