How to get date from a long string in excel cell (1 Viewer)

rehanemis

Registered User.
Local time
Today, 18:50
Joined
Apr 7, 2014
Messages
195
Hi,
I would like to take out the second date from the excel cell as shown below in bold:
3/10/2022 Listing removed $1,225$1/sqft
Source: Zillow Rental Network Premium Reporta problem
2/15/2022 Listed for rent $1,225 (+23.1%)$1/sqft
Source: Zillow Rental Network Premium Reporta problem
8/13/2019 Listing removed $995
Source: American Real PM - STL Reporta problem
6/5/2019 Listed for rent $995
Source: American Real PM - STL Reporta problem

So there are many dates in single cell with text, I would like to take out the second one only. So date might change i.e 12/12/2022 etc.

any suggestion to accomplish it with formula/functions?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:50
Joined
May 7, 2009
Messages
19,169
create a function that will Split() your Cell value.
now have a Counter to check if the value IsDate().
if the counter reaches to 2 then exit and return the date.
Code:
Public Function fnSecondDate(ByVal sText As String)
Dim var, v, ret
Dim i As Integer
sText = Replace$(sText, Chr$(13) & Chr$(10), " ")
sText = Replace$(sText, Chr$(13), " ")
sText = Replace$(sText, Chr$(10), " ")
Do While InStr(1, sText, "  ") <> 0
    sText = Replace$(sText, "  ", " ")
Loop
var = Split(sText)
ret = ""
For Each v In var
    If IsDate(v) Then
        i = i + 1
        
    End If
    If i = 2 Then
        ret = CDate(v)
        Exit For
    End If
Next
fnSecondDate = ret
End Function
 

rehanemis

Registered User.
Local time
Today, 18:50
Joined
Apr 7, 2014
Messages
195
Great!
Thank you so much!
Can you please write comments so that I can understand what is going on in the function?
 

June7

AWF VIP
Local time
Today, 06:50
Joined
Mar 9, 2014
Messages
5,423
The code does:

1. replaces all cr/lf characters with a space as well as double spaces with a single space (I don't know why the need to loop for replacing double spaces)
2. uses Split() function to feed every string between the spaces into an array
3. loops the array and tests if each string is a valid date structure
4. returns the second date structure encountered
 

rehanemis

Registered User.
Local time
Today, 18:50
Joined
Apr 7, 2014
Messages
195
Hi,
I would like to take out the second date from the excel cell as shown below in bold:
3/10/2022 Listing removed $1,225$1/sqft
Source: Zillow Rental Network Premium Reporta problem
2/15/2022 Listed for rent $1,225 (+23.1%)$1/sqft
Source: Zillow Rental Network Premium Reporta problem
8/13/2019 Listing removed $995
Source: American Real PM - STL Reporta problem
6/5/2019 Listed for rent $995
Source: American Real PM - STL Reporta problem

So there are many dates in single cell with text, I would like to take out the second one only. So date might change i.e 12/12/2022 etc.

any suggestion to accomplish it with formula/functions?
Suppose I would like to get number from first line which is 1,225 so how do I change the code? as Number might be of 3 digits or some time 5 digits.
 

June7

AWF VIP
Local time
Today, 06:50
Joined
Mar 9, 2014
Messages
5,423
Could use IsNumeric() to test if string is valid number. First have to get rid of dollar sign and comma. More Replace() actions.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:50
Joined
May 7, 2009
Messages
19,169
i think you need to change it to something more "comprehensive".
Code:
Public Function fnGetDataFromText(ByVal sText As String, ByVal sWhatToGet As String, ByVal Ordinal As Integer) As Variant
' arnelgp
'
' parameters:
'
'       sText       = the source text/string
'       sWhatToGet  = any valid text as below:
'
'           "Date"
'           "Text"
'           "Number"
'
'       Ordinal     = 1 or 2 or 3 (first, second, or third)
'
Dim var, v, ret
Dim i As Integer
fnGetDataFromText = ""
sText = Trim$(sText)
sWhatToGet = Trim$(sWhatToGet)
If InStr(1, ";Date;Text;Number;", ";" & sWhatToGet) = 0 Or Len(sText) = 0 Then
    Exit Function
End If
sText = Replace$(sText, Chr$(13) & Chr$(10), " ")
sText = Replace$(sText, Chr$(13), " ")
sText = Replace$(sText, Chr$(10), " ")
Do While InStr(1, sText, "  ") <> 0
    sText = Replace$(sText, "  ", " ")
Loop
var = Split(sText)
ret = ""
For Each v In var
    v = Trim$(v & "")
    Select Case sWhatToGet
        Case Is = "Date"
            If IsDate(v) Then
                i = i + 1
            End If
        Case Is = "Text"
            If Not IsDate(v) And Not IsNumeric(v) Then
                i = i + 1
            End If
        Case Is = "Number"
            v = Replace$(v, ",", "")
            v = Trim$(v)
            If Not IsDate(v) Then
                v = getNumber(v, Ordinal, i)
                If Not IsNull(v) Then
                    v = CDbl(v)
                End If
            End If
    End Select
    If i = Ordinal Then
        If sWhatToGet = "Date" Then
           fnGetDataFromText = CDate(v)
        Else
            fnGetDataFromText = v
        End If
        Exit For
    End If
Next
End Function

Private Function getNumber(ByVal s As String, ByVal Ordinal As Integer, ByRef Counter As Integer) As Variant
Dim var
getNumber = Null
With CreateObject("VBScript.RegExp")
    .Pattern = "[0-9.]+"
    .Global = True
    .ignorecase = True
    For Each var In .Execute(s)
        Counter = Counter + 1
        If Counter = Ordinal Then
            Exit For
        End If
    Next
End With
If Counter <> 0 Then
    getNumber = var.Value
End If
End Function
 

Users who are viewing this thread

Top Bottom