Find only numerics after a special character and separate with a comma

[dkreisb]

Hello,

I'm new here, but I have reviewed dozens of responses regarding the title of this post. Unfortunately, I haven't been able to get the results I desire. I am looking for numbers that appear after a special character, I only want to retain those numbers and I want to separate them with a comma. Here are some samples of my data and desired results.

Data:
BIGBOY2;#138;#BIGBOY2SAVE;#2478;#SHARED ENVIRONMENT;#7613
TEA1BAG;#1234;#BIGBOY2;#1388;#SHARED ENVIRONMENT3;#7615
TINCUP;#2;#BIG88BOY;#1233

Desired Results:
138,2478,7613 (notice the number 2 from BIGBOY2 and BIGBOY2SAVE was not retained)
1234,138,7615 (notice the numbers 1, 2 & 3 in TEA1BAG, BIGBOY2 and SHARED ENVIRONMENT3 were not retained)
2,1233 (notice the number 88 in BIG88BOY was not retained)

The numbers following the pound sign could be of length 1-6. I've tried various text clipping functions and UDFs, but I haven't been able to crack it. Thoughts?

 

[vds1]

One way would be to use text to column > Delimited > Other Type # > Next > Finish

Next you can delete unwanted column, Replace ; and use =CONCATENATE function to get your desired results..

 

[TheCman81]

Based on your sample data and assuming you always have Number - Text - Number - Text - Number etc this should work

=SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(A2,"#",REPT(" ",99)),99,99)&MID(SUBSTITUTE(A2,"#",REPT(" ",99)),300,99)&MID(SUBSTITUTE(A2,"#",REPT(" ",99)),500,99)," ",""),";",",")

 

[vds1]

Cman,

your formula wont work if the data is like below

TEA1BAG;#1234;#BIGBOY2;#1388;#SHARED ENVIRONMENT3;#7615;#BIGBOY2;#1388;#SHARED ENVIRONMENT3;#7615

 

[TheCman81]

@vds1

That's why I stated based on the sample data, if the data string is longer then the formula can be updated to accomodate

I'll wait for OP reply thanks

 

[dkreisb]

That worked, but it may not work if i have more than 3 IDs (numbers). I could just repeat for the potential number of occurences, right?

Thank you!

 

[TheCman81]

Is there a limit to the number of ID's?

 

[dkreisb]

I don't know the exact conditions as I have only review 1000 rows of a 50,000 row table. I will extract the entire table and test to see, and I will let you know what I find. I suspect 4 or 5 may be the limit.

 

[vds1]

You Can also try this UDF

=SplitData(A1)

[/COLOR]Function SplitData(Rng As Range) As String
    Dim Cnt As Integer
    Dim sStr As String
    sStr = Replace(Rng.Value, ";#", ",")
    For i = 0 To UBound(Split(sStr, ","))
        If IsNumeric(Split(sStr, ",")(i)) = True Then
            If SplitData = "" Then
                SplitData = Split(sStr, ",")(i)
            Else
                SplitData = SplitData & "," & Split(sStr, ",")(i)
            End If
        End If
    Next i
End Function


[COLOR=#0000cd]

 

[dkreisb]

Bingo! I found at least 4 occurrences of the sequence in some of my data, and the UDF worked on those.

Thank you all very much!