Hi,

Am Fri, 20 Feb 2015 09:18:07 -0800 (PST) schrieb :

We get data in a report that is indecipherable. I need to extract all switch numbers following a "$" that preceed a "-1".
for example from the following output I need the following results. Note that each long string is contained in one cell.

try it with an UDF:

Function myString(myRng As Range) As String
Dim varData As Variant
Dim i As Long, Start As Long

varData = Split(myRng, "-1")
If UBound(varData) = 0 Then
myString = "Null"
Else
For i = 0 To UBound(varData) - 1
Start = InStrRev(varData(i), "$") + 1
myString = myString & Mid(varData(i), Start) & ", "
Next
End If
If myString < "Null" Then
myString = Left(myString, Len(myString) - 2)
End If
End Function

If your data in in column A then call the function with
=myString(A1)
and copy down


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional