Extracting Date from String

manojsahooin

New Member
Joined
Jan 3, 2014
Messages
25
Hi Everyone

I am facing problem extracting date and amount to two different cells from a text string. in the Below mention example you can see there are two different date format, however after extracting dates can be in any format.

Below is how my data looks

146.3012-3-2013abc def ghi
-146.30dfctest12/3/2013abc def ghi


Result needed in below format

Column A Column B
146.30 12-3-2013
-146.30 12/3/2013

appreciate any help in this regard.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,149
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Hi Everyone

I am facing problem extracting date and amount to two different cells from a text string. in the Below mention example you can see there are two different date format, however after extracting dates can be in any format.

Below is how my data looks

146.3012-3-2013abc def ghi
-146.30dfctest12/3/2013abc def ghi


Result needed in below format

Column A Column B
146.30 12-3-2013
-146.30 12/3/2013

appreciate any help in this regard.

Given that there are no fixed delimiters separating the date from the surrounding text, I think you are going to have to tell the possible date formats that you know could be in the text so that we can build specific filters for them. Also, in your examples, is the 12 a month number or a day number?
 

manojsahooin

New Member
Joined
Jan 3, 2014
Messages
25
Hello Rick

The dates i mentioned above is in dd/mm/yyyy format. The two possible formats are 12-3-2013 & 12/3/2013 one separated by "-" and one by "/".

Thanks for your fast response.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,149
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Hello Rick

The dates i mentioned above is in dd/mm/yyyy format. The two possible formats are 12-3-2013 & 12/3/2013 one separated by "-" and one by "/".

Thanks for your fast response.

I can give you a formula for the currency part, but I need to give you a UDF (user defined function) for retrieving the date. Assuming your text string is in cell A1, this formula will extract the currency amount...

=LEFT(A1,FIND(".",A1)+2)+0

You will need to format the cell as a number with two decimal places (in order to display trailing zeros. Here is the UDF for extracting the date

Code:
Function GetDate(ByVal S As String) As Date
  Dim AfterCents As String, DayNumber As String, Parts() As String
  AfterCents = Replace(Mid(S, InStr(S, ".") + 3), "-", "/")
  Parts = Split(AfterCents, "/")
  DayNumber = Mid(Right(Parts(0), 2), 2 + (Right(Parts(0), 2) Like "*##"))
  GetDate = DateSerial(Left(Parts(2), 4), Parts(1), DayNumber)
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetDate just like it was a built-in Excel function. For example,

=GetDate(A1)

This formula will return the date serial number, so you will have to format the cell you place it in as a Date using whatever date display format you want.

NOTE: If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 

manojsahooin

New Member
Joined
Jan 3, 2014
Messages
25
I can give you a formula for the currency part, but I need to give you a UDF (user defined function) for retrieving the date. Assuming your text string is in cell A1, this formula will extract the currency amount...

=LEFT(A1,FIND(".",A1)+2)+0

You will need to format the cell as a number with two decimal places (in order to display trailing zeros. Here is the UDF for extracting the date

Code:
Function GetDate(ByVal S As String) As Date
  Dim AfterCents As String, DayNumber As String, Parts() As String
  AfterCents = Replace(Mid(S, InStr(S, ".") + 3), "-", "/")
  Parts = Split(AfterCents, "/")
  DayNumber = Mid(Right(Parts(0), 2), 2 + (Right(Parts(0), 2) Like "*##"))
  GetDate = DateSerial(Left(Parts(2), 4), Parts(1), DayNumber)
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetDate just like it was a built-in Excel function. For example,

=GetDate(A1)

This formula will return the date serial number, so you will have to format the cell you place it in as a Date using whatever date display format you want.

NOTE: If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.





Thanks a tone, it worked marvelously. How you do that... its really awesome. How do you write the UDF, please suggest how can i learn to write UDF.

Thanks Again
Manoj
 

Forum statistics

Threads
1,136,354
Messages
5,675,303
Members
419,560
Latest member
g3org

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
Top