Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find WorksheetFunction - finding text within text
I have a long list of strings. I want to return the first part of each
string, up to the first space or foward slash ("/"). I can't seem to use the Find function correctly (does it only work for searching through ranges?). Here is my code - not the most elegant... any ideas? Function ExtractClient(mystr As String) As String Dim i As Single Dim SpacePos As Single Dim SlashPos As Single Dim NoSpace As Boolean Dim NoSlash As Boolean Dim MinDelim As Integer NoSpace = False NoSlash = False With Application.WorksheetFunction If .IsError(.Find(" ", mystr, 1)) = True Then 'no space in string NoSpace = True Else SpacePos = .Find(" ", mystr, 1) End If If .IsError(.Find("/", mystr, 1)) = True Then 'no slash in string NoSlash = True Else SlashPos = .Find("/", mystr, 1) End If If NoSlash = True And NoSpace = True Then ExtractClient = "" Else If NoSlash = True Then ExtractClient = .Left(mystr, SpacePos - 1) Else If NoSpace = True Then ExtractClient = .Left(mystr, SlashPos - 1) Else MinDelim = .Min(SpacePos, SlashPos) ExtractClient = .Left(mystr, MinDelim - 1) End If End If End If End With End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find WorksheetFunction - finding text within text
don't use the worksheetfunction Find. Use the VBA function Instr
If you must go with your current code, remove WorksheetFunction. so just Application. remains. then, given it is otherwise written correctly, it will work. -- Regards, Tom Ogilvy "Marcotte A" wrote in message ... I have a long list of strings. I want to return the first part of each string, up to the first space or foward slash ("/"). I can't seem to use the Find function correctly (does it only work for searching through ranges?). Here is my code - not the most elegant... any ideas? Function ExtractClient(mystr As String) As String Dim i As Single Dim SpacePos As Single Dim SlashPos As Single Dim NoSpace As Boolean Dim NoSlash As Boolean Dim MinDelim As Integer NoSpace = False NoSlash = False With Application.WorksheetFunction If .IsError(.Find(" ", mystr, 1)) = True Then 'no space in string NoSpace = True Else SpacePos = .Find(" ", mystr, 1) End If If .IsError(.Find("/", mystr, 1)) = True Then 'no slash in string NoSlash = True Else SlashPos = .Find("/", mystr, 1) End If If NoSlash = True And NoSpace = True Then ExtractClient = "" Else If NoSlash = True Then ExtractClient = .Left(mystr, SpacePos - 1) Else If NoSpace = True Then ExtractClient = .Left(mystr, SlashPos - 1) Else MinDelim = .Min(SpacePos, SlashPos) ExtractClient = .Left(mystr, MinDelim - 1) End If End If End If End With End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
Finding text in a cell and returning a value based on that text | Excel Discussion (Misc queries) | |||
can you find specific text in a string ignoring any other text | Excel Discussion (Misc queries) | |||
Finding Specific Text in a Text String | Excel Worksheet Functions | |||
Unable to get the Text property of the WorksheetFunction class | Excel Programming |