Extract Data to Date Format

[muhammad susanto]

hi all..

i need excel formula how to extract data this below "

sample raw	after formula (date format)...dd/mm/yy
7143/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.19 Nopember 2012	19/11/2012
100/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.10 Januari 2012	10/01/2012
45/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.26 Maret 2012	26/03/2012

<tbody>
</tbody>

note : red font just clue..
not using vba

any assistance, thanks in advance..

m.susanto

 

[muhammad susanto]

i think, use Find & Replace fitur

Find : *Tgl.
Replace : All

It's done...

 

[Peter_SSs]

It does assume that 'Tgl.' always precedes the date,

It (as with all the 'FIND' suggestions) also assumes "Tgl." does not occur earlier in the string as well. That is quite likely the case given the sample data but the SUBSTITUTE method would be a little more robust in that regard. I must admit though that I used it here more from habit of extracting something from the end of a string.

I'd thought about using MiD, but hadn't thought to set the final value to a large value.

Just a note that it doesn't need to be a very large value in this case. If the dates were English, 17 would do for that length parameter since September is the longest written month - 9 characters + 4 for the year + 2 (maximum) for the day + 2 spaces

 

[Peter_SSs]

i think, use Find & Replace fitur

Find : *Tgl.
Replace : All

It's done...

Great idea. I had meant to mention that idea in my post but your first post seemed to indicate you wanted a formula & then it slipped my mind.
If using Find/Replace with your data, just the following would do too.
Find: *.

 

[muhammad susanto]

hi Peter, you are great!!

 

[pjmorris]

Hi Peter, thanks for the explanation. I've seen Substitute used in a number of posts, but I haven't yet adopted it! Time to do some more learning.

Thanks again.