Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help on sumproduct() - sum by searching for a common sub string in | Excel Worksheet Functions | |||
Searching for string in other cells | Excel Worksheet Functions | |||
searching and summing using string within cell | Excel Discussion (Misc queries) | |||
Searching a String | Excel Programming | |||
searching a string with a partial string | Excel Programming |