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

dkreisb

New Member
Joined
Jul 1, 2014
Messages
7
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

Well-known Member
Joined
Oct 5, 2011
Messages
1,186
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..
 
Last edited:

TheCman81

Well-known Member
Joined
Feb 28, 2012
Messages
535
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

Well-known Member
Joined
Oct 5, 2011
Messages
1,186
Cman,

your formula wont work if the data is like below

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

TheCman81

Well-known Member
Joined
Feb 28, 2012
Messages
535
@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

New Member
Joined
Jul 1, 2014
Messages
7
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

Well-known Member
Joined
Feb 28, 2012
Messages
535
Is there a limit to the number of ID's?
 

dkreisb

New Member
Joined
Jul 1, 2014
Messages
7
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

Well-known Member
Joined
Oct 5, 2011
Messages
1,186
You Can also try this UDF

=SplitData(A1)

Code:
[/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

New Member
Joined
Jul 1, 2014
Messages
7
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!
:)
 

Forum statistics

Threads
1,081,841
Messages
5,361,635
Members
400,642
Latest member
tekster23

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top