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

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

"William Francis" wrote in
message ...
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

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)


"William Francis" wrote in
message ...
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