Help with String Manipulation

[garyvancecook]

Trying to parse a field with c:\external\tampa\files\divisions. example
Want to only put the 2nd directory. i.e tampa

This is what I was thinking. I would need to subtract out the c:\external and get a right string up to the next slash. Please assist.
=LEFT(A1,12)
=RIGHT(A1,(LEN(A1)-(SEARCH("\",A1)-((LEN(A1)-(SEARCH("\",A1)))))))

 

[jbeaucaire]

It's a long way to go for it, but it works as is, someone will most likely offer a UDF to do it more directly. If the string you noted was in A1, then this pulls out "tampa":

=LEFT(RIGHT(SUBSTITUTE(A1,"\","@",1),LEN(SUBSTITUTE(A1,"\","@",1))-FIND("\",SUBSTITUTE(A1,"\","@",1))),FIND("\",RIGHT(SUBSTITUTE(A1,"\","@",1),LEN(SUBSTITUTE(A1,"\","@",1))-FIND("\",SUBSTITUTE(A1,"\","@",1))))-1)

 

[Jonmo1]

Try

=LEFT(REPLACE(A1,1,FIND("\",A1,FIND("\",A1)+1),""),FIND("\",REPLACE(A1,1,FIND("\",A1,FIND("\",A1)+1),""))-1)

 

[riteshkp]

I would suggest - use the excel function that is listed under Data Menu, its called Text To Columns and use "\" as the delimiter.

 

[jbeaucaire]

LOL, let me offer the UDF myself.

Press Alt-F11 to open the VBeditor
Insert > Module
Paste in this new function code:

Function MyExtract(MyText As String, ItemNo As Integer, FrontOrBack As String, Optional MySeparator As String) As String

' This function will scan a string containing at least TWO words, each separated
' by a specified character and extract one of the words. It can extract the word
' (or SubString) counting from either the Back or Front of the text.
' [www.meadinkent.co.uk]
Dim LenText As Integer, n As Integer, CountSpaces As Integer
Dim MySt As Integer, MyFin As Integer, MyStep As Integer, Mk1 As Integer, Mk2 As Integer

' MySeparator was an optional parameter
If Len(MySeparator) = 0 Then MySeparator = " "

LenText = Len(MyText)
' You cannot extract a word if length is LT 3 chars
If LenText < 3 Then
MyExtract = "*"
GoTo MyEndBit
End If

' set the direction in which the text is examined
If UCase(FrontOrBack) = "F" Then
MySt = 2
MyFin = LenText - 1
MyStep = 1
Else
MyFin = 2
MySt = LenText - 1
MyStep = -1
End If

' identify the position of characters matching the separator
For n = MySt To MyFin Step MyStep
If Mid(MyText, n, 1) = MySeparator Then
CountSpaces = CountSpaces + 1
If CountSpaces = ItemNo - 1 Then Mk1 = n
If CountSpaces = ItemNo Then Mk2 = n
End If
Next n

If CountSpaces = 0 Then
MyExtract = "*"
GoTo MyEndBit
End If

If UCase(FrontOrBack) = "B" Then
n = Mk1
Mk1 = Mk2
Mk2 = n
End If

If Mk2 = 0 Then Mk2 = LenText + 1
Mk1 = Mk1 + 1

MyExtract = Mid(MyText, Mk1, Mk2 - Mk1)
MyEndBit:
End Function

(Source)
Then Alt-F11 to close the editor and save your sheet.

Now use the new function MyExtract like so:

=MyExtract(A1,3,"f","\")

First parameter is the cell
Second parameter is the word or string count we want (separated by the delimiter in param 4)
Third param is "f" or "b" meaning count from the front or back of the string
Fourth param is the delimiter that separates the string.

 

[Aladin Akyurek]

Another option...

=REPLACE(LEFT(A1,FIND("#",SUBSTITUTE(A1,"\","#",3))-1),1,FIND("#",SUBSTITUTE(A1,"\","#",2)),"")

 

[garyvancecook]

Thanks everyone.