Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Truncate last five characters
How would you code to truncate the .hml or .html from the tail end of a
filename string? Jesse |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to truncate text characters | Excel Discussion (Misc queries) | |||
Trying to truncate or separate the first 3 characters/digits of co | Excel Discussion (Misc queries) | |||
truncate | Excel Worksheet Functions | |||
fill or truncate to a certain number of characters in a cell | Excel Worksheet Functions | |||
linking cells in Excel 2003. How to not truncate to 255 characters. | Excel Discussion (Misc queries) |