Separating workbook name from extension

[Guest]

Hi guys,

Can anyone please let me know whether any code exists which separates a
workbook name from its extension (e.g. a spreadsheet named "test.xls" would
be separated into "test" and ".xls")?

Thanks in advance,
Will

 

[Greg Koppel]

filename = "test.xls"
newName = Left(filename, len(filename) - 4) & vbTab & Right(filename, 4)

 

[Bob Phillips]

VBA or worksheet function .

Worksheet function, with only one embedded dot
=LEFT(A2,FIND(".",A2)-1) and
=MID(A2,FIND(".",A2)+1,32)

or if there can be multiple dots
=LEFT(A1,FIND("~",SUBSTITUTE(A1,".","~",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))
-1)
=MID(A1,FIND("~",SUBSTITUTE(A1,".","~",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))+
1,32)

with VBA


Function SplitName(InVal As Range, ext As Boolean)
Dim ipos As Long
Dim sText As String

If TypeName(InVal) = "Range" Then
sText = InVal.Value
Else
sText = InVal
End If
For ipos = Len(sText) To 1 Step -1
If Mid(InVal.Value, ipos, 1) = "." Then
Exit For
End If
Next ipos

If ext Then
SplitName = Mid(sText, ipos + 1, 32)
Else
SplitName = Left(sText, ipos - 1)
End If
End Function


Pass the text or the text cell, and indicate whether you want the file
extension or not.


pass

--

HTH

RP
(remove nothere from the email address if mailing direct)