Spliting a string

[Arnie]

hi folks i have been able to read in a file name but i want to extract from
it (in this instance "need" the format is always
Word1_Word2_Word3_Word4_Word5.xls

i can get rid of the .xls but i need to extract the "Word4" from the file name

code below gets me the filename then i need to split that

any ideas?

ta in advance

Dim intPos As Integer
Dim intPosSave As Integer

intPos = 1
Do
intPos = InStr(intPos, strFile, "\")
If intPos = 0 Then
Exit Do
Else
intPos = intPos + 1
intPosSave = intPos - 1
End If
Loop
GetFileName = Trim$(Mid$(strFile, intPosSave + 1))


Word4= GetFileName
Word4= Replace(Site, ".xls", "", , -1)

 

[Arnie]

Sorry should read like this

i have been able to read in a file name but i want to extract from
it the filename for example

Word1_Word2_Word3_Word4_Word5.xls

i can get rid of the .xls but i need to extract the "Word4" from the file name

code below gets me the filename then i need to split that

any ideas?

ta in advance

Dim intPos As Integer
Dim intPosSave As Integer

intPos = 1
Do
intPos = InStr(intPos, strFile, "\")
If intPos = 0 Then
Exit Do
Else
intPos = intPos + 1
intPosSave = intPos - 1
End If
Loop
GetFileName = Trim$(Mid$(strFile, intPosSave + 1))


Word4= GetFileName
Word4= Replace(Site, ".xls", "", , -1)

 

[Stefan Hoffmann]

hi Arnie,

Word1_Word2_Word3_Word4_Word5.xls
i can get rid of the .xls but i need to extract the "Word4" from the file name
[..]
Word4= GetFileName
Word4= Replace(Site, ".xls", "", , -1)

This is not correct, as the extension for this name
"word1.xls_word2.xls_word3.slx" is either "xls_word2.xls_word3.slx" or
"slx". Traditionally it is the second one.

http://en.wikipedia.org/wiki/Filename_extension

Getting the 4th word:

FileNameWithoutExt = "Word1_Word2_Word3_Word4_Word5"

Dim a() As String

a() = Split(FileNameWithoutExt, "_")

MsgBox "Word 4 is " & a(3)





mfG
--> stefan <--

 

[Arnie]

Thanks Stefan

worked a treat

hi Arnie,

Word1_Word2_Word3_Word4_Word5.xls
i can get rid of the .xls but i need to extract the "Word4" from the file name
[..]
Word4= GetFileName
Word4= Replace(Site, ".xls", "", , -1)

This is not correct, as the extension for this name
"word1.xls_word2.xls_word3.slx" is either "xls_word2.xls_word3.slx" or
"slx". Traditionally it is the second one.

http://en.wikipedia.org/wiki/Filename_extension

Getting the 4th word:

FileNameWithoutExt = "Word1_Word2_Word3_Word4_Word5"

Dim a() As String

a() = Split(FileNameWithoutExt, "_")

MsgBox "Word 4 is " & a(3)





mfG
--> stefan <--