Extract Data to Date Format

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
2,077
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
hi all..

i need excel formula how to extract data this below "
sample rawafter formula (date format)...dd/mm/yy
7143/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.19 Nopember 201219/11/2012
100/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.10 Januari 201210/01/2012
45/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.26 Maret 201226/03/2012

<tbody>
</tbody>

note : red font just clue..
not using vba

any assistance, thanks in advance..

m.susanto
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi, hopefully those spellings of the month names match your regional settings, if so try:

=TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",99)),99))+0

Custom format the cell containing the formula as dd/mm/yyyy
 
Upvote 0
Try this. Note that I have changed your month spellings to English so it would work on my machine.
Formula in B2 copied down, then format column B with your preferred date format.

Excel Workbook
AB
1sample rawafter formula
27143/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.19 November 201219/11/2012
3100/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.10 January 201210/01/2012
445/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.26 March 201226/03/2012
Extract date
 
Last edited:
Upvote 0
Peter & FormR,

Am I missing something, why use Substitute? My solution was to use this: =RIGHT(A1,LEN(A1)-FIND("Tgl.",A1)-3)*1 It does assume that 'Tgl.' always precedes the date, but thats what the example shows. The *1 then coerces the string into a date (assuming the cell is appropriately formated).

Regards
 
Upvote 0
Oh that is certainly true with Excel - I just wondered if there was a particular benefit i'm missing.
 
Upvote 0
Oh that is certainly true with Excel - I just wondered if there was a particular benefit i'm missing.

Peter may have a different view - but I don't think so.. here is another option (saves a function call)

Excel Workbook
AB
1sample rawAfter
27143/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.19 November 201219/11/2012
3100/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.10 January 201210/01/2012
445/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.26 March 201226/03/2012
Sheet2
 
Upvote 0
Like that; I'd thought about using MiD, but hadn't thought to set the final value to a large value.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top