Extract Certain Text

Rikuk · #1 06-09-2009, 05:54 AM

Within my access database I have a field that contains job update information.

Is it possible to extract from this field the date if the field contains the pharse Deferral Online.

Examples of the text Text in Red required extracting

SANDRA ADVISED TO SEND OVER

Job deferred by BNEI01, At 08/Jun/2009 1328, From 08/Jun/2009 1719 To 08/Jun/2009 1719

Job Referred and Authorised by RDAV01, At 08/Jun/2009 1331

Deferral Online on 08/06/2009 at 1549 From 08/06/2009 171900 To 26/06/2009 2359

LOGGED WITH GIDGE AT SUPPLIER ON REFERENCE 581868

Job deferred by MDEA01, At 08/Jun/2009 0915, From 08/Jun/2009 0540 To 09/Jun/2009 0540

Deferral Online on 08/06/2009 at 1048 From 08/06/2009 054000 To 08/06/2009 1700

Rik

bhill · #2 06-09-2009, 08:40 AM

You can do it, but you'll need a custom function. I wasn't sure if the samples you provided were in one record (with carriage returns in them) or if they appear on multiple rows. This function will account for either. You can call it within a query like so: Find_Date(someinputdata).

Code:

Public Function Find_Date(stIn As Variant) As String
    Dim stQual As String
    Dim stQual1 As String
    Dim intPos As Integer
    Dim intPos1 As Integer

    stQual = "Deferral Online"
    stQual1 = "To "

    If Len(stIn) = 0 Then 'If there is no input, end the function
        Find_Date = ""
        Exit Function
    End If

    If InStr(1, stIn, stQual) <> 0 Then 'Determine if stQual exists in the input
        intPos = InStr(1, stIn, stQual)    'Position of stQual
        intPos1 = InStr(intPos, stIn, stQual1)    'Position of stQual1 from stQual position
        Find_Date = Mid(stIn, intPos1 + Len(stQual1), 10)    'isolates the date value from stQual1 Position and returns it
    Else 'if stQual doesn't exist in the input, return empty
        Find_Date = ""
    End If

End Function

Welcome to the Excel Forum

Forum Statistics