UDF for extracting Date from Junk Text

[rumshar]

Hi All,
My manager wants me to help him with a UDF to extract date from junk text like below. Does anyone have magic wand here?

Junk Text Clean Date
Joined on Jan 2nd - 19 days 1/2/2014
Joined on Jan 4th - 17 days 1/4/2014
Joined on Jan 4th - 17 days 1/4/2014
Joined on Jan 4th - 17 days 1/4/2014
Joined on Jan 16th - 5 days 1/16/2014
Joined on Jan 16th - 5 days 1/16/2014
Joined on Jan 16th - 5 days 1/16/2014
Joined on Jan 16th - 5 days 1/16/2014
Joined on Jan 16th - 5 days 1/16/2014
Joined on Jan 16th - 5 days 1/16/2014
Joined on Jan 18th - 3 days 1/18/2014
Joined on Jan 18th - 3 days 1/18/2014
Joined on Jan 19th - 2 days 1/19/2014
Joined on Jan 19th - 2 days 1/19/2014

With Regards
Rudra

 

[shrivallabha]

To get the data in Text format:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),10))

And number format (real date)

=--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),10))

 

[Hui]

Or use Text to Columns

Select the Data
Data
Text to Columns
Fixed Width
Drag the vertical lines so there is only a single vertical line between days and the date
Apply

 

[SirJB7]

Hi, rumshar!
Try this:

Option Explicit

Function dBossIsAlwaysRightExceptIfIAmFriendOfHisBoss(psString As String) As Date
    ' constants
    ' declarations
    Dim D As Date
    ' start
    ' process
    D = CDate(Right(psString, InStr(StrReverse(psString), " ") - 1))
    ' end
    dBossIsAlwaysRightExceptIfIAmFriendOfHisBoss = D
End Function

Regards!
PS: Tell your boss' boss to give me a call.

 

[Debraj]

Hi @rumshar ,

U think, you should have upload the sample file.. as due to text distraction, your data looks like in a single cell.
If your data is like below,

Joined on Jan 2nd - 19 days
Joined on Jan 4th - 17 days
Joined on Jan 4th - 17 days
Joined on Dec 25th - 17 days
Joined on Jan 16th - 5 days
Joined on Jan 16th - 5 days
Joined on Jan 16th - 5 days
Joined on Mar 22nd - 5 days
Joined on Jan 16th - 5 days
Joined on Jan 16th - 5 days
Joined on Jan 18th - 3 days
Joined on Jan 18th - 3 days
Joined on Jan 19th - 2 days
Joined on Jan 19th - 2 days

and you want result as provided, then you can use the below formula...

PS: Assuming your system's default setting is mm-dd-yyyy

=LOOKUP(99^99,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),{1,100,200,300,400},200)),"st",""),"nd",""),"rd",""),"th",""),"-","XX")+0)

 

[SirJB7]

@Debraj (ex-Roy)
Hi, buddy!
It'd be a Pi day joke if it's like that...
Regards!
PS: BTW, nobody read the topic title? U-D-F. Which doesn't stand for un<something> damn formula...

 

[SirJB7]

Hi, rumshar!

If @Debraj (ex-Roy) is right, the formula solution might be this:
=EXTRAE(A1;11;HALLAR("-";A1)-14)&" "&AÑO(HOY()) -----> in english: =MID(A1,11,SEARCH("-",A1)-14)&" "&YEAR(TODAY())
and the VBA one this:

Option Explicit

Function dBossIsAlwaysRightExceptIfIAmFriendOfHisBoss(psString As String) As Date
    ' constants
    ' declarations
    Dim D As Date
    ' start
    ' process
    D = CDate(Mid(psString, 11, InStr(psString, "-") - 14) & " " & Year(Now()))
    ' end
    dBossIsAlwaysRightExceptIfIAmFriendOfHisBoss = D
End Function

Regards"

 

[rumshar]

@SirJB7, this is exactly I..sorry My Boss...was looking for..thanks a lot.
@ All, thank you very much for all your efforts.
I am just wondering why wasn't I using this forum earlier?.

With Regards
Rudra

 

[SirJB7]

Hi, rumshar!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
PS: Because we're a secret elite community almost impossible to find, hidden among the cyberspace nebulae.

 

[rumshar]

Hi sirjb7
sorry for the confusion, I had to come here again...It was my mistake that I didn't upload the file....Please see the attachment for my good manager's requirement.
Data in 1st Col is Junk date which should be converted to valid date with the help of a UDF(as in col B). The clean Date was typed manullay in column B.

I Pledge to upload my sample file whenever I create a thread

With Regards
Rudra

 

[Debraj]

Hi Rudra,

As per your attachment, @SirJB7 's code is still working..
Your provided dates are not JUNK, they are in a PATTERN.
Please upload few variation, if below in your actual data doesn't work,

 

[SirJB7]

Hi, rumshar!
I agree with @Debraj (ex-Roy), the provided code works with your data which responds to a pattern and it's not junk. If found any issue, please detail which one and post or upload the related data.
Regards!