Re: How to trim cell information

Tech-Archive recommends: Fix windows errors by optimizing your registry

It's been over 20 years since I touched a Regular Expression (on a true Unix box), so I really don't remember anything; hence, I don't know how to do this any more. However, you really only have to look at text after the last backslash, then if it has a dot in it, take only the part before it. If you can't do that in one regular expression, can't you "chain" two regular expressions together to do this?

Rick


"Ron Rosenfeld" <ronrosenfeld@xxxxxxxxxx> wrote in message news:4cjqh3loc246inrvst1ps37up2n8dfqei0@xxxxxxxxxx
On Mon, 22 Oct 2007 16:11:43 -0400, "Rick Rothstein \(MVP - VB\)"
<rickNOSPAMnews@xxxxxxxxxxxxxxxxx> wrote:


I could modify mine to handle the absence of a file type extension.

I think you should do so. That way the OP (or others following the thread)
will have a choice. Besides, if the OP (or others readers) come from a
Regular Expression background, they will more than likely find your solution
far more appealing than mine.

Rick

To include filenames that might not include a type extension, a starting point
is to modify sPat (pattern) so that the characters after the last dot "." are
optional:

=====================================
Option Explicit
Function reFilename(str As String) As String
Dim re As Object
Dim mc As Object
Const sPat As String = "\\([^\\\,.]+)(\.[^.]+)?$"

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
If re.test(str) = True Then
Set mc = re.Execute(str)
reFilename = mc(0).submatches(0)
End If
End Function
======================================

HOWEVER, this may no longer give accurate matches. Windows long file names may
include the dot "." So really you'd need to exclude all possible file type
extensions, but not other endings that are not.

I'm no expert on allowable file type extensions in Windows.

What I did below was assume that a final "." followed by three or four
characters that are letters, numbers or underscore would represent a file type
extension. But that other lengths or including other characters would not.

It may well be that other characters should be added to the allowable list.
This can be easily done.

In any event, it does become a lot more complex with long filenames that might
or might not include an extension:

=====================================
Option Explicit
Function reFilename(str As String) As String
Dim re As Object
Dim mc As Object
Const sPat As String = "\\([^\\]+)((\.\w{3,4})|(\.[^.]*))$"

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
If re.test(str) = True Then
Set mc = re.Execute(str)
reFilename = mc(0).submatches(0) & mc(0).submatches(3)
End If
End Function
=========================================

It'd become even more complicated if we allowed other file systems!

Best,
--ron

.

Relevant Pages

  • Re: How to trim cell information
    ... To include filenames that might not include a type extension, ... Function reFilename(str As String) As String ... Dim re As Object ... I'm no expert on allowable file type extensions in Windows. ...
    (microsoft.public.excel.programming)
  • Re: Save As wk4
    ... Dim ws As Worksheet ... I thought I could just change the extension to wk4 above. ... Can someone help with code to change the file type to wk4 as ...
    (microsoft.public.excel.programming)
  • Re: Save As wk4
    ... | Dim ws As Worksheet ... I thought I could just change the extension to wk4 above. ... Can someone help with code to change the file type to wk4 ...
    (microsoft.public.excel.programming)
  • RE: Hyperlink to Find Filenames in a Folder
    ... will iterate through an array of file names and use a regular expression to ... Dim expressionMatch as Boolean ... If txtLNm = "" Then ... Set objRegEx = New RegExp ...
    (microsoft.public.access.formscoding)
  • Re: Need help with regular expression
    ... Function FindStrings(sString, sBeginning, sEnd, bIsCaseSensitive) ... ' end with an exact match to sEnd. ... Dim dFound 'holds substrings found, ... Set regEx = New RegExp ' Create a regular expression. ...
    (microsoft.public.scripting.vbscript)