searching through a string?
 

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
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=538371

searching through a string?
 

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.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=538371

searching through a string?
 

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

searching through a string?
 

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
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=538371

searching through a string?
 

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

searching through a string?
 

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

searching through a string?
 

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