searching through a string?

[funkymonkUK]

is there away of searching through a string?

I have a long string about 130 characters as I have a LONG directory structure. but what I want to do is exact a small part of it.

eg. c:\1_london for may-06.xls
eg. c:\2_Kent for may-06.xls

I have two of the following files what I want to extract is just "London" and just " kent" now i thought of using left/Mid/Right but as you can see they are different lens.

Any help much appreciate

[funkymonkUK]

sorry i forgot to mention that what I am doing is run code which extracts a list of files in a specific folder it then needs to check the file names against a list and then mark off that it exists.

Example as above

I have a list which is

london
kent
Sussex


What I want to do is show that London and kent file exists but not sussex.

[Ivan Raiminius]

Hi,

combine mid and worksheetfunction.find

supposing directory is a string that contains the path, outstring is
result:

outstring = mid(directory,worksheetfunction.Find
("_",directory)+1,worksheetfunction.Find ("
",directory,worksheetfunction.Find
("_",directory))-worksheetfunction.Find ("_",directory)-1)

Regards,
Ivan

[funkymonkUK]

I have the following.

Sub test1()
' Find what the users shared drive is named
Dim mainpath As String
Dim lenPath As Integer
Dim outstring As String
mainpath = ThisWorkbook.FullName
lenPath = Len(mainpath) - 17

outstring = Mid(mainpath, WorksheetFunction.Find("_", mainpath) + 1, WorksheetFunction.Find _
(" ", mainpath, WorksheetFunction.Find("_", mainpath)) - WorksheetFunction.Find("_", mainpath) - 1)

mainpath = ""
lenPath = Blank
End Sub

I have run this and get the following error message "1004. Unable to get the find property of the worksheetfuntion class" any ideas?

[funkymonkUK]

how would this work if i have say the following file?

c:\test\1_South East London for May-06.xls

would this just extract "South"?

I need to extract where it came from and then mark off that the source's file has been received.

there is a common thing between the strings in that it will all have _ before the name and then the word "for"" after the name.

[Andrew Taylor]

You can use the Instr function to check whether
a given string exists in the filename:

If Instr(UCase(strFilename), "LONDON")) > 0 Then
' filename contains London
Else
' it doesn't
End if

Note that Instr is case-sensitive, hence the use of UCase.
It returns the position at which the searched-for string
begins, or 0 if not found.


funkymonkUK wrote:
> how would this work if i have say the following file?
>
> c:\test\1_South East London for May-06.xls
>
> would this just extract "South"?
>
> I need to extract where it came from and then mark off that the
> source's file has been received.
>
>
> --
> funkymonkUK
> ------------------------------------------------------------------------
> funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
> View this thread: http://www.excelforum.com/showthread...hreadid=538371

[Tom Ogilvy]

Function ExtractString(s As String)
Dim s1 As String, ipos As Long
Dim jpos As Long
ipos = InStr(1, s, "_", vbTextCompare)
jpos = InStr(1, s, "for", vbTextCompare)
s1 = Mid(s, ipos + 1, jpos - ipos - 2)
ExtractString = s1
End Function

Demo'd from the immediate window:
? ExtractString("c:\test\1_South East London for May-06.xls")
South East London
? extractString("c:\1_london for may-06.xls")
london

--
Regards,
Tom Ogilvy


"funkymonkUK" wrote:

>
> how would this work if i have say the following file?
>
> c:\test\1_South East London for May-06.xls
>
> would this just extract "South"?
>
> I need to extract where it came from and then mark off that the
> source's file has been received.
>
>
> --
> funkymonkUK
> ------------------------------------------------------------------------
> funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
> View this thread: http://www.excelforum.com/showthread...hreadid=538371
>
>

[Tom Ogilvy]

Function ExtractString(s As String)
Dim s1 As String, ipos As Long
Dim jpos As Long
ipos = InStr(1, s, "_", vbTextCompare)
jpos = InStr(1, s, "for", vbTextCompare)
s1 = Mid(s, ipos + 1, jpos - ipos - 2)
ExtractString = s1
End Function

Demo'd from the immediate window:
? ExtractString("c:\test\1_South East London for May-06.xls")
South East London
? extractString("c:\1_london for may-06.xls")
london

--
Regards,
Tom Ogilvy


"funkymonkUK" wrote:

>
> how would this work if i have say the following file?
>
> c:\test\1_South East London for May-06.xls
>
> would this just extract "South"?
>
> I need to extract where it came from and then mark off that the
> source's file has been received.
>
>
> --
> funkymonkUK
> ------------------------------------------------------------------------
> funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
> View this thread: http://www.excelforum.com/showthread...hreadid=538371
>
>