How to split a string with delimiter?

[Guest]

I have a need to split a string with the help of a delimiter. I don't know
the position of the delimiter like I have file name that could be any number
of characters, I want to find out the file extension, like .doc, .gif how can
I get it?

something like <somefunction>(filename, ".")
should give me all characters after "." in filename,

Thank you for your help in advance,
-Me

 

[Allen Browne]

Instr() will give you the position of a character.

InstrRev() will give you the position of the last dot in the string.

 

[Douglas J Steele]

Surprising enough, the function is named Split. <g>

However, recognize that it's possible to have more periods in the name than
just at the end of the string.

You could create a function like this:

Function FileExtension(FileName As String) As String
Dim varParts As Variant

varParts = Split(FileName, ".")
FileExension = varParts(UBound(varParts))

End Function

Another option is to use the InStrRev function to find the position of the
last period, and then the Mid function to return everything from the last
period on:

Mid(FileName, InStrRev(FileName, ".") + 1)

 

[Guest]

Thank you Allen, this is what I was looking for, wish there was a single
function solution though.

-Me

 

[Guest]

Doug,

Great explanation, on dot.

Appreciate your help!
-Me

 

[Albert D.Kallal]

Thank you Allen, this is what I was looking for, wish there was a single
function solution though.

There is


fileextension = split("myword.doc",".")(1)

The above file exteniosn would be set to doc in the above.....

 

[Douglas J Steele]

There is


fileextension = split("myword.doc",".")(1)

The above file exteniosn would be set to doc in the above.....

That works fine if there's only one period in the file name. Windows,
however, lets you use multiple periods:

This is a .txt file not a .mdb file.txt

is a valid file name.

(While I don't have much sympathy for people who choose file names like
that, a more realistic example is folder names in 8.3 format, so that if
you've got a complete file path, you can't rely on using your split function
above: the extension may not be the 2nd element in the array)