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.

Output Results
$1$2$3$4$5$6$7$8$9$10$11$25$50$51$52$99 null

$1-1$2$3$4$5$6$7$8$9$10$11$12$13$14$15$16$25$26$27$28 $30$31$32$33$50$51$52$53$54$55$60$61$80$85$97$98$9 9 1

$1$2$3$4$5$6$7$8$9$10$11$12$13$14$15$16$25$26$27$2 8$30-1$31-1$32$33$40$50$51$52-1$53$54$55$60$61$70$80$85-1$97$98$99 30,31,52,85

$1$2$3$4$5$6$7$8$9$10$11$12$13$14$15$25$26$27$28$3 0-1$31-1$32$50$51$52$53$54$55$80$85$97$98-1$99-1 30,31,98,99

$1$2$3$4$5$6$7$8$9$10$11$12$13$14$15$16$25$26$27$2 8$30-1$31-1$32$33$40$50-1$51$52$53$54-1$55$60$61$70$80$85-1$97$98$99-1 30,31,50,54,85,99

$1$2$3$4$5$6$7$8$9$10$11$12$13$14$15$16$25$26$27$2 8$30-1$31-1$32$33$40-1$50$51$52$53$54$55$60$61$70$80$85$97$98$99-1 30,31,40,99

$1$2$3$4$5$6$7$8$9$10$11$12$13$14$15$16$25$26$27$2 8$30-1$31-1$32$33$40$50$51$52$53$54$55$60$61$70$80$85$97$98-1$99-1 30,31,98,99

$1$2$3$4$5$6$7$8$9$10$11$12$13$14$15$16$25$26$27$2 8$30-1$31-1$32$33$40$50$51$52$53$54$55$60$61$70$80$85$97$98-1$99-1 30,31,98,99

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

On Friday, February 20, 2015 at 12:47:40 PM UTC-5, Claus Busch wrote:
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

Fantastic, Claus! You hit it out of the ballpark! Many thanks!

hi,

Function MySplit(rng As Range) As String
x = Split(rng, "$")
For i = LBound(x) To UBound(x)
If Not IsError(Application.Find("-", x(i))) Then r = r & Split(x(i), "-")(0) & " "
Next
MySplit = r
End Function

isabelle

On Friday, February 20, 2015 at 1:11:47 PM UTC-5, isabelle wrote:
hi,

Function MySplit(rng As Range) As String
x = Split(rng, "$")
For i = LBound(x) To UBound(x)
If Not IsError(Application.Find("-", x(i))) Then r = r & Split(x(i), "-")(0) & " "
Next
MySplit = r
End Function

isabelle

Thanks Isabelle, that gets it done as well!