Re: How to trim cell information

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Mon, 22 Oct 2007 10:58:02 -0700, BZeyger <BZeyger@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

I have information in a column with a file path. I would like it to only
display the file name. Can this be done?

For example:

C:\test excel\PROPS\abcdefg.xls
C:\test excel\PROPS\dfsdagg.xls
C:\test excel\PROPS\12324vcs.xls

I would like it to display only the file name without the extension or path

abcdefg
dfsdagg
12324vcs

For a formula, assuming the file types have three characters, try:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1)-
FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-
LEN(SUBSTITUTE(A1,"\",""))))-4)

For a UDF, which allows for file types of three or four characters, you could
try this:

===============================
Option Explicit
Function reFilename(str As String) As String
Dim re As Object
Dim mc As Object
Const sPat As String = "\\([^\\\,]+)\..{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)
End If
End Function
==================================
--ron
.

Quantcast