How can I strip out the text

[Geoff Codd]

I have the following value

V:\Neil\B4014_1.csv

And I need to strip out the part between the Last "\" and the ".", So I am left with B4014_1

Any help much appreciated

Thanks
Geoff

 

[Mile-O]

Public Function GetFile(sFile As String) As String

    On Error GoTo Err_GetFile ' turn on error handler
    
    Dim strTemp As String ' for manipulating file name
    Dim intStart As Integer, intStop As Integer ' for finding positions
    
    strTemp = StrReverse(sFile) ' reverse the filepath
    
    intStart = InStr(1, strTemp, ".") 'get position of .
    intStop = InStr(1, strTemp, "\") ' get position of \
    
    ' get the filename removing path and extension
    strTemp = Mid(strTemp, intStart + 1, (intStop - 1) - intStart)
    
    GetFile = StrReverse(strTemp) ' reverse the value and return

' error handling routine
Exit_GetFile:
    strTemp = vbNullString
    Exit Function
    
Err_GetFile:
    GetFile = vbNullString
    Resume Exit_GetFile
    
End Function

 

[raskew]

Mile-O,

Elegant strategy!

Here's another possibility.

From the debug window:

myStr = "V:\Neil\B4014_1.csv"
myStr = nthBlock(nthBlock(myStr, 3, "\"),1,".")
? myStr
B4014_1

...with nthBlock() being:

Function nthBlock(ByVal pItem As String, _
                  pInt As Integer, _
                  pDelim As String) As String
'********************************************
'Purpose:   Return the Nth delimited block of
'           text in a string
'Coded by:  raskew
'Inputs:    from debug window
'           myStr = "The*quick*brown*fox* _
                    jumped*over*the*lazy*dog."
'           ? nthBlock(mystr, 4, "*")
'Output:    fox
'********************************************

Dim i       As Integer
Dim strHold As String
Dim strKeep As String

    strHold = Trim(pItem) + IIf(Right(strHold, Len(pDelim)) <> pDelim, pDelim, "")
    
    For i = 1 To pInt
        
        strKeep = Left(strHold, InStr(strHold, pDelim))
        
        strHold = Mid(strHold, InStr(strHold, pDelim) + Len(pDelim))
    
    Next i
    
    nthBlock = Left(strKeep, InStr(strKeep, pDelim) - 1)

End Function

Bob

 

[Mile-O]

I suppose it can be even easier (easier as no need for the function but directly into the query)

MyFile: Left(Mid([MyField], InStrRev([MyField], "\") + 1), InStrRev(Mid([MyField], InStrRev([MyField], "\") + 1), ".") - 1)

 

[Geoff Codd]

thank you very much, I wasn't expecting such a indeph solution
Geoff

 

[Mile-O]

I wasn't expecting such a indeph solution

What were you expecting?

 

[dcx693]

How about:

Function GetFileName(strFullname As String)

    Do Until (InStr(strFullname, "\")) = 0
        strFullname = Right(strFullname, Len(strFullname) - (InStr(strFullname, "\")))
    Loop
    
    GetFileName = Left(strFullname, InStr(1, strFullname, ".") - 1)

End Function

Call it like this: GetFileName("V:\Neil\B4014_1.csv"), or in a query like: GetFileName([myfilename]). Some error handling would be wise also, since some filenames don't have a "." in them.

 

[Kodo]

yet another idea.

Function GetFinalString(myvar As String) As String
Dim Newstring As Variant, lastpart As String, finalstring As String

Newstring = Split(myvar, "\")

lastpart = Newstring(UBound(Newstring))

finalstring = Left(lastpart, Len(lastpart) - 4)

GetFinalString = finalstring

End Function

 

[dcx693]

Great ideas everyone! Just wanted to note that the InstrRev and Split functions aren't available in Access 97. (Also, extensions aren't always 3 characters long, but they probably are in this case.)

 

[Mile-O]

yet another idea.

Function GetFinalString(myvar As String) As String
Dim Newstring As Variant, lastpart As String, finalstring As String

Newstring = Split(myvar, "\")

lastpart = Newstring(UBound(Newstring))

finalstring = Left(lastpart, Len(lastpart) - 4)

GetFinalString = finalstring

End Function

Nice. Added a slight change to cope with different length extensions. Hopefully that should work.

Function GetFinalString(myvar As String) As String 
    Dim Newstring As Variant, lastpart As String, finalstring As String 

    Newstring = Split(myvar, "\") 
    lastpart = Newstring(UBound(Newstring)) 
    finalstring = Left(lastpart, Len(lastpart) - Len(Mid(lastpart, InStr(1, lastpart, ".") + 1))) 

    GetFinalString = finalstring 

End Function

 

[Kodo]

Sweet, Mile! I didn't take into consideration longer extensions.

 

[tempus4]

I found your solutions much to my delight and the following:-

MyFile: Left(Mid([MyField], InStrRev([MyField], "\") + 1), InStrRev(Mid([MyField], InStrRev([MyField], "\") + 1), ".") - 1)

worked like a dream in my Access query until I realised that my server doesn't support InStrRev ... Yeah! how slack is that!

It does support InStr but that's the opposite side of the string and me being the Access moron that I am can't work out how to get InStr to work... any help will be hugely appreciated!

Thanks, Cherry

 

[mresann]

All-purpose Text Extractor

Here is an all-purpose text string extractor. Simiply put, it takes three inputs:

1. The whole source string.
2. The string to the LEFT of the intended target string.
3. The string to the RIGHT of the indended target string.

There is a fourth optional input, which extract the target string form any part of the string.

This is a public function, and can be dropped into any bas module, VBA or VB.

Public Function fncExtractData(ByVal strCurrentLine As String, _
                               ByVal strLeft As String, _
                               ByVal strRight As String, _
                               Optional ByVal lngStart As Long _
                               ) As String
'-----------------------------------------------------------------------------------------
' Procedure : fncExtractData
' Created   : Jan 3 2005 11:40
' Reference : fncExtractData*
' Author    : Michael Reese
' Input(s)  : Line of text which contains data, text to immediate left of data to extract,
'             text to immediate right of data to extract, optional starting point
' Output(s) : Extracted data
' Purpose   : To extract one element of data from a text line
'-----------------------------------------------------------------------------------------

'|<------ 90-character width -------------------------------- 90-character width ------->|

PROC_DECLARATIONS:
   Dim strExtractData  As String
   Dim strExtender     As String
   
PROC_START:
   On Error GoTo PROC_ERROR
   strExtender = Chr(222) & Chr(243)
   
PROC_MAIN:
   If strLeft = Space(0) Then
      strCurrentLine = strExtender & strCurrentLine
      strLeft = strExtender
   ElseIf strRight = Space(0) Then
      strCurrentLine = strCurrentLine & strExtender
      strRight = strExtender
   End If
   
   If lngStart < 1 Then
      lngStart = 1
   End If
   
   strExtractData = Left(Right(strCurrentLine, _
         (Len(strCurrentLine) - (InStr(lngStart, strCurrentLine, strLeft) + Len(strLeft)) + 1)), _
         InStr(Right(strCurrentLine, (Len(strCurrentLine) - (InStr(lngStart, strCurrentLine, strLeft) + Len(strLeft)) + 1)), _
         strRight) - 1)
   
PROC_EXIT:
   fncExtractData = strExtractData
   Exit Function

PROC_ERROR:
   MsgBox "Error " & Err.Number & " (" & _
           Err.Description & ")" & vbCrLf & vbCrLf & _
           "Procedure: fncExtractData" & vbCrLf & _
           "Module: basLineSplitter"
   GoTo PROC_EXIT

                               
End Function

Here are some examples (in all cases, strCurrentLine = "ABCDEFGHIJKLDEFMNOJKLPQR"

Example 1.

strLeft = "GH"
strRight = "LDE"

the return is "IJK"

Example 2.

strLeft = ""
strRight = "FG"

the return is "ABCDE"

Example 3

strLeft = "DEF"
strRight = "JKL"

the return is "GHI"

...however,

Example 4

strLeft = "DEF"
strRight = "JKL"
lngStart = 10 (Starts the search at the designated point in the string)

the return is "MNO"


IN SHORT, there is no need to write customized text extraction procedures. I use this function in text extraction form web page data sorts, for instance, and it can be called recursively to actually extract nested strings conveniently.

 

[tempus4]

Thanks Mresann,

the function looks great and I can understand a fair bit of it but I think I should have put "Access Moron" in bold as my talents go so far as simple use of Access db's using asp pages to access the data. I really liked

MyFile: Left(Mid([MyField], InStrRev([MyField], "\") + 1), InStrRev(Mid([MyField], InStrRev([MyField], "\") + 1), ".") - 1)

because I could place it in the access query field and it worked #simple#.
I have little idea how to use this function... I tried using an asp page but got errors after the "A"... didn't really expect it to work anyway

Thanks again for any people offering me some assistance.

Cherry