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 |
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 |