Extracting Date from String

[manojsahooin]

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.

 

[Rick Rothstein]

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]

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]

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

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]

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

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