Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default Truncate last five characters

How would you code to truncate the .hml or .html from the tail end of a
filename string?

Jesse


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,089
Default Truncate last five characters

Jesse

from a reply by Tom Ogilvy to a similar question (slightly modified):

sfilename = Left(sfilename,len(sFilename)-5)

Regards

Trevor


"Jesse Hamilton" wrote in message
news:D0Srb.15952$jy.10631@clgrps13...
How would you code to truncate the .hml or .html from the tail end of a
filename string?

Jesse




  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,123
Default Truncate last five characters

You can use the left function for example

Sub test()
Dim fname As String
fname = "C:\testfile.htm"
MsgBox fname
MsgBox Left(fname, Len(fname) - 4)
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Jesse Hamilton" wrote in message news:D0Srb.15952$jy.10631@clgrps13...
How would you code to truncate the .hml or .html from the tail end of a
filename string?

Jesse




  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Truncate last five characters

Maybe something like:

If Instr(sFilename,".htm") 0 then
If Right(sFilename,5) = ".html" then
sFilename = Left(sFilename,len(sFilename)-5)
else
sFilename = Left(sFilename,len(sFilename)-4)
End if
End if

--
Darren
"Jesse Hamilton" wrote in message
news:D0Srb.15952$jy.10631@clgrps13...
How would you code to truncate the .hml or .html from the tail end of a
filename string?

Jesse




  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Truncate last five characters

Great thought. Since you are using Instr


If instr(sfilename,".htm") Then ' any non-zero number is TRUE
sfilename = Left(sfilename,instr(sfilename,".htm")-1)
End if

--
Regards,
Tom Ogilvy



Darren Hill wrote in message
...
Maybe something like:

If Instr(sFilename,".htm") 0 then
If Right(sFilename,5) = ".html" then
sFilename = Left(sFilename,len(sFilename)-5)
else
sFilename = Left(sFilename,len(sFilename)-4)
End if
End if

--
Darren
"Jesse Hamilton" wrote in message
news:D0Srb.15952$jy.10631@clgrps13...
How would you code to truncate the .hml or .html from the tail end of a
filename string?

Jesse








  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Truncate last five characters

That's clever :)

--
Darren
"Tom Ogilvy" wrote in message
...
Great thought. Since you are using Instr


If instr(sfilename,".htm") Then ' any non-zero number is TRUE
sfilename = Left(sfilename,instr(sfilename,".htm")-1)
End if

--
Regards,
Tom Ogilvy



Darren Hill wrote in message
...
Maybe something like:

If Instr(sFilename,".htm") 0 then
If Right(sFilename,5) = ".html" then
sFilename = Left(sFilename,len(sFilename)-5)
else
sFilename = Left(sFilename,len(sFilename)-4)
End if
End if

--
Darren
"Jesse Hamilton" wrote in message
news:D0Srb.15952$jy.10631@clgrps13...
How would you code to truncate the .hml or .html from the tail end of

a
filename string?

Jesse








  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default Truncate last five characters

Thanks guys, much appreciate the help.

Jesse


"Darren Hill" wrote in message
...
That's clever :)

--
Darren
"Tom Ogilvy" wrote in message
...
Great thought. Since you are using Instr


If instr(sfilename,".htm") Then ' any non-zero number is TRUE
sfilename = Left(sfilename,instr(sfilename,".htm")-1)
End if

--
Regards,
Tom Ogilvy



Darren Hill wrote in message
...
Maybe something like:

If Instr(sFilename,".htm") 0 then
If Right(sFilename,5) = ".html" then
sFilename = Left(sFilename,len(sFilename)-5)
else
sFilename = Left(sFilename,len(sFilename)-4)
End if
End if

--
Darren
"Jesse Hamilton" wrote in message
news:D0Srb.15952$jy.10631@clgrps13...
How would you code to truncate the .hml or .html from the tail end

of
a
filename string?

Jesse










  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default Truncate last five characters

The previous solutions all suffer from assumptions that the file
extension is X characters long (e.g. 3 or 4), or is such-and-such
character string (e.g. "htm"). Those kinds of assumptions will cause
debugging later on when reality intrudes. ;-)

Here's a more generic way to do this that will work with file
extensions of any length, no length, or containing multiple periods
(hence the StrReverse). It's coded as a function so it can be called
where ever needed w/o rewriting the code. If you don't know how to
integrate the function into your code you can just take the code
inside the function (wow, that sounded weird!).

__________________________________________________ ______________

Sub joe()
Dim sFilename As String ' original filename

sFilename = "Hello World"
MsgBox (sFilename & vbCrLf & StripExtension(sFilename))

sFilename = "Hello World.htm"
MsgBox (sFilename & vbCrLf & StripExtension(sFilename))

sFilename = "Hello World.html"
MsgBox (sFilename & vbCrLf & StripExtension(sFilename))

sFilename = "Hello World.Peace.doc"
MsgBox (sFilename & vbCrLf & StripExtension(sFilename))

End Sub
__________________________________________________ ______________

Function StripExtension(Filename As String) As String

Dim sEmanelif As String ' reversed filename
Dim sFileNoExt As String ' filename w/o extension

If InStr(1, Filename, ".") = 0 Then
StripExtension = Filename
Else
' reverse Filename string to get extension at front
sEmanelif = StrReverse(Filename)
' take from 1 past period onward
sEmanelif = Mid(sEmanelif, InStr(sEmanelif, ".") + 1)
' reverse back to original and place in function result
StripExtension = StrReverse(sEmanelif)
End If

End Function
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Truncate last five characters

No, the previous posts answered the question asked and didn't assume xl2000
like you are assuming (which could cause problems later when reality
intrudes).

How would you code to truncate the .hml or .html from the tail end of a

filename string?

--
Regards,
Tom Ogilvy

Ladislav Ligart wrote in message
om...
The previous solutions all suffer from assumptions that the file
extension is X characters long (e.g. 3 or 4), or is such-and-such
character string (e.g. "htm"). Those kinds of assumptions will cause
debugging later on when reality intrudes. ;-)

Here's a more generic way to do this that will work with file
extensions of any length, no length, or containing multiple periods
(hence the StrReverse). It's coded as a function so it can be called
where ever needed w/o rewriting the code. If you don't know how to
integrate the function into your code you can just take the code
inside the function (wow, that sounded weird!).

__________________________________________________ ______________

Sub joe()
Dim sFilename As String ' original filename

sFilename = "Hello World"
MsgBox (sFilename & vbCrLf & StripExtension(sFilename))

sFilename = "Hello World.htm"
MsgBox (sFilename & vbCrLf & StripExtension(sFilename))

sFilename = "Hello World.html"
MsgBox (sFilename & vbCrLf & StripExtension(sFilename))

sFilename = "Hello World.Peace.doc"
MsgBox (sFilename & vbCrLf & StripExtension(sFilename))

End Sub
__________________________________________________ ______________

Function StripExtension(Filename As String) As String

Dim sEmanelif As String ' reversed filename
Dim sFileNoExt As String ' filename w/o extension

If InStr(1, Filename, ".") = 0 Then
StripExtension = Filename
Else
' reverse Filename string to get extension at front
sEmanelif = StrReverse(Filename)
' take from 1 past period onward
sEmanelif = Mid(sEmanelif, InStr(sEmanelif, ".") + 1)
' reverse back to original and place in function result
StripExtension = StrReverse(sEmanelif)
End If

End Function



  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default sorry :-$

My apologies to all, that did come across as condescending. I was
trying to show Jesse that the more you go from a specific,
"works-only-if", solution to generic solution, the better. And if you
look at the flow of the answers, they were going that way anyway. :-)

"Tom Ogilvy" wrote in message ...
No, the previous posts answered the question asked and didn't assume xl2000
like you are assuming ...

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
formula to truncate text characters MarkT Excel Discussion (Misc queries) 6 June 4th 09 04:19 PM
Trying to truncate or separate the first 3 characters/digits of co Jim Excel Discussion (Misc queries) 4 January 13th 06 02:51 PM
truncate tamar Excel Worksheet Functions 1 July 19th 05 10:32 PM
fill or truncate to a certain number of characters in a cell Jan Buckley Excel Worksheet Functions 1 March 16th 05 04:46 PM
linking cells in Excel 2003. How to not truncate to 255 characters. GarryFerg Excel Discussion (Misc queries) 5 December 8th 04 04:33 PM


All times are GMT +1. The time now is 02:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"