Supertramp;
Try this:
=EXTRACTELEMENT(A1;2;" ")
Where A1 is de cel to extract an element from.
2 means the second element.
" " means that the second element is seperated by a space.
Place this code in a VBA module.
Function EXTRACTELEMENT(Txt, n, Separator) As String
' Returns the nth element of a text string, where the elements are
separated by a specified separator character
Dim Txt1 As String, TempElement As String
Dim ElementCount As Integer, i As Integer
Txt1 = Txt
' If space separator, remove excess spaces
If Separator = Chr(32) Then Txt1 = Application.Trim(Txt1)
' Add a separator to the end of the string
If Right(Txt1, Len(Txt1)) <> Separator Then _
Txt1 = Txt1 & Separator
' Initialize
ElementCount = 0
TempElement = ""
' Extract each element
For i = 1 To Len(Txt1)
If Mid(Txt1, i, 1) = Separator Then
ElementCount = ElementCount + 1
If ElementCount = n Then
' Found it, so exit
EXTRACTELEMENT = TempElement
Exit Function
Else
TempElement = ""
End If
Else
TempElement = TempElement & Mid(Txt1, i, 1)
End If
Next i
EXTRACTELEMENT = ""
End Function
Mark.
More Excel ?
www.rosenkrantz.nl or (e-mail address removed)
