Separating all 5 digit numbers from a mixed text and number cell string

R

robredo101

New Member
Joined
Jun 4, 2014
Messages
1
Hi guys,

Wondering if you guys would be able to provide some assistance with this..
[TABLE="width: 1159"]
<TBODY>[TR]
[TD]I'm trying to sort through mixed data in a column and want to separate all the 5 digit numbers (there could be more than one in each cell) from the mixed text and numbers in each cell (see below for example data). Assume each line to be a cell in Excel.

I have used to formula (below) but it is not giving me the results I want in all cases. In fact, it only gives me the first number listed in each cell and isn't totally accurate.

=MID($R2,MIN(FIND({1,2,3,4,5,6,7,8,9},$R2&1234567890)),5)[/TD]
[/TR]
[TR]
[TD]
Is there a formula which can split the mixed text and numbers column (below) and list all the 5 digit numbers either side by side so i can use text to columns, or in their own cells? The data i have provided below extends for 14000 lines in Excel and every cell is unique.

Any help here would be much appreciated :-)[/TD]
[/TR]
[TR]
[TD]
CONNECTOR;BNC;STR CRIMP PLUG;RA700</SPAN>[/TD]
[/TR]
[TR]
[TD]27423 x 1,Card;isa-eth access;1660sm. 27424 x 1,Card;isa-gbe access/2;
1660sm. 29039 x 1,Module;opto trx sfp 1.25gbe lx ddm.</SPAN>[/TD]
[/TR]
[TR]
[TD]22780 x 1,panel alarm & pwr distr;48vdc; no circuit.
22783 x 10,circuit breaker;1p;20a;240v;6ka;c-curve.</SPAN>[/TD]
[/TR]
[TR]
[TD]29039 x 8,module;opto trx sfp 1.25gbe lx ddm.
27424 x 2,card;isa-gbe access/2;1660sm.</SPAN>[/TD]
[/TR]
[TR]
[TD]1660 subrack & cards</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP No 27832</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP.29218 x1.</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP.28956 x1.</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP.22776 x6.</SPAN>[/TD]
[/TR]
[TR]
[TD]25675
CABLE;MA5100 H511CESC TO DDF;120 OHM;30M</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP.24810 x3,24814 x6.</SPAN>[/TD]
[/TR]
[TR]
[TD]sap 24809 x 7</SPAN>[/TD]
[/TR]
[TR]
[TD]sap 21153 x 1</SPAN>[/TD]
[/TR]
[TR]
[TD]sap 27364 x 2</SPAN>[/TD]
[/TR]
[TR]
[TD]sap 24809 x 1</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP.25044 x10,19259 x10,26036 x5.</SPAN>[/TD]
[/TR]
[TR]
[TD]sap 28717 x 1, sap 24810 x 12, sap 27917 x 4</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP.28788 x1,28954 x1,27831 x1,27834 x1,27834 x1,27339 x1,27835 x1,
27839 x1,27840 x2,27502 x4,28085 x1.</SPAN>[/TD]
[/TR]
[TR]
[TD]Cable Management , Waterfall Kit. As per invoice 6 items ordered 6 items
supplied.</SPAN>[/TD]
[/TR]
[TR]
[TD]20703 x 2,Module disconn;10 pr in/10 pr out;cat5e</SPAN>[/TD]
[/TR]
[TR]
[TD]24810 x 2,Card;2xoc-48/stm-16;vt1.5/lo sfp,ome6500
24814 x 4,Module;occcc-48/stm-16;sr-1/i-16.1 xct</SPAN>[/TD]
[/TR]
[TR]
[TD]28952 x 2, isa;es16-b;c/w 1 x 3al81915ab or
27424 x 2, card;isa-gbe access/2;1660m</SPAN>[/TD]
[/TR]
[TR]
[TD]27424 x 1,card;isa-gbe access/2;1660sm</SPAN>[/TD]
[/TR]
[TR]
[TD]28952 x 2,isa;es16-b;c/w 1 x 3al81915ab or
27424 x 2,card;isa-gbe access/2;1660sm</SPAN>[/TD]
[/TR]
[TR]
[TD]28952 x 2,isa;es16-b;c/w 1 x 3al81915ab or
27424 x 1,card;isa-gbe access/2;1660sm
27423 x 1,card;isa-ethaccess;1660sm</SPAN>[/TD]
[/TR]
[TR]
[TD]27502 x 4,card;dummy plate w20;22220mm;1660sm</SPAN>[/TD]
[/TR]
[TR]
[TD]22832 x 2,shelf;splice/patch;iru;19in rack mtg</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP.90629 x1,27972 x1,28084 x1,28426 x2,29259 x1,28089 x1,27339 x2,
28956 x1,27338 x1,27341 x2,27364 x4,27363 x1,27502 x17.</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP.25753 x2. [TABLE="width: 1159"]
<TBODY>[TR]
[TD]
</SPAN>[/TD]
[/TR]
[TR]
[TD]
</SPAN>[/TD]
[/TR]
[TR]
[TD]CONNECTOR;BNC;STR CRIMP PLUG;RA700</SPAN>[/TD]
[/TR]
[TR]
[TD]27423 x 1,Card;isa-eth access;1660sm. 27424 x 1,Card;isa-gbe access/2;
1660sm. 29039 x 1,Module;opto trx sfp 1.25gbe lx ddm.</SPAN>[/TD]
[/TR]
[TR]
[TD]22780 x 1,panel alarm & pwr distr;48vdc; no circuit.
22783 x 10,circuit breaker;1p;20a;240v;6ka;c-curve.</SPAN>[/TD]
[/TR]
[TR]
[TD]29039 x 8,module;opto trx sfp 1.25gbe lx ddm.
27424 x 2,card;isa-gbe access/2;1660sm.</SPAN>[/TD]
[/TR]
[TR]
[TD]1660 subrack & cards</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP No 27832</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP.29218 x1.</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP.28956 x1.</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP.22776 x6.</SPAN>[/TD]
[/TR]
[TR]
[TD]25675
CABLE;MA5100 H511CESC TO DDF;120 OHM;30M</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP.24810 x3,24814 x6.</SPAN>[/TD]
[/TR]
[TR]
[TD]sap 24809 x 7</SPAN>[/TD]
[/TR]
[TR]
[TD]sap 21153 x 1</SPAN>[/TD]
[/TR]
[TR]
[TD]sap 27364 x 2</SPAN>[/TD]
[/TR]
[TR]
[TD]sap 24809 x 1</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP.25044 x10,19259 x10,26036 x5.</SPAN>[/TD]
[/TR]
[TR]
[TD]sap 28717 x 1, sap 24810 x 12, sap 27917 x 4</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP.28788 x1,28954 x1,27831 x1,27834 x1,27834 x1,27339 x1,27835 x1,
27839 x1,27840 x2,27502 x4,28085 x1.</SPAN>[/TD]
[/TR]
[TR]
[TD]Cable Management , Waterfall Kit. As per invoice 6 items ordered 6 items
supplied.</SPAN>[/TD]
[/TR]
[TR]
[TD]20703 x 2,Module disconn;10 pr in/10 pr out;cat5e</SPAN>[/TD]
[/TR]
[TR]
[TD]24810 x 2,Card;2xoc-48/stm-16;vt1.5/lo sfp,ome6500
24814 x 4,Module;occcc-48/stm-16;sr-1/i-16.1 xct</SPAN>[/TD]
[/TR]
[TR]
[TD]28952 x 2, isa;es16-b;c/w 1 x 3al81915ab or
27424 x 2, card;isa-gbe access/2;1660m</SPAN>[/TD]
[/TR]
[TR]
[TD]27424 x 1,card;isa-gbe access/2;1660sm</SPAN>[/TD]
[/TR]
[TR]
[TD]28952 x 2,isa;es16-b;c/w 1 x 3al81915ab or
27424 x 2,card;isa-gbe access/2;1660sm</SPAN>[/TD]
[/TR]
[TR]
[TD]28952 x 2,isa;es16-b;c/w 1 x 3al81915ab or
27424 x 1,card;isa-gbe access/2;1660sm
27423 x 1,card;isa-ethaccess;1660sm</SPAN>[/TD]
[/TR]
[TR]
[TD]27502 x 4,card;dummy plate w20;22220mm;1660sm</SPAN>[/TD]
[/TR]
[TR]
[TD]22832 x 2,shelf;splice/patch;iru;19in rack mtg</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP.90629 x1,27972 x1,28084 x1,28426 x2,29259 x1,28089 x1,27339 x2,
28956 x1,27338 x1,27341 x2,27364 x4,27363 x1,27502 x17.</SPAN>[/TD]
[/TR]
[TR]
[TD]SAP.25753 x2.</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Maybe something like this ...a VBA solution
Previously posted by apo from Wirribi
Code: 
Private Sub MM1()
    Dim objRegex, n
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        .Pattern = "\d{5}"
        For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
            Set myMatches = .Execute(Cells(i, 1))
            For Each n In myMatches
                If Cells(i, 2).Value = "" Then
                    Cells(i, 2).Value = n
                Else
                    Cells(i, 2).Value = Cells(i, 2).Value & ";" & n
                End If
            Next n
        Next i
    End With
End Sub
 
Upvote 0
Hello Rob,

I am looking to purchase a Alcatel 1660 Subrack immediately. Are you selling these units? I work for a company called Digitechx. Please contact me at rachelm@digitechx.com


robredo101 said:
Hi guys,

Wondering if you guys would be able to provide some assistance with this..
[TABLE="width: 1159"]
<tbody>[TR]
[TD]I'm trying to sort through mixed data in a column and want to separate all the 5 digit numbers (there could be more than one in each cell) from the mixed text and numbers in each cell (see below for example data). Assume each line to be a cell in Excel.

I have used to formula (below) but it is not giving me the results I want in all cases. In fact, it only gives me the first number listed in each cell and isn't totally accurate.

=MID($R2,MIN(FIND({1,2,3,4,5,6,7,8,9},$R2&1234567890)),5)[/TD]
[/TR]
[TR]
[TD]Is there a formula which can split the mixed text and numbers column (below) and list all the 5 digit numbers either side by side so i can use text to columns, or in their own cells? The data i have provided below extends for 14000 lines in Excel and every cell is unique.

Any help here would be much appreciated :-)[/TD]
[/TR]
[TR]
[TD]CONNECTOR;BNC;STR CRIMP PLUG;RA700[/TD]
[/TR]
[TR]
[TD]27423 x 1,Card;isa-eth access;1660sm. 27424 x 1,Card;isa-gbe access/2;
1660sm. 29039 x 1,Module;opto trx sfp 1.25gbe lx ddm.[/TD]
[/TR]
[TR]
[TD]22780 x 1,panel alarm & pwr distr;48vdc; no circuit.
22783 x 10,circuit breaker;1p;20a;240v;6ka;c-curve.[/TD]
[/TR]
[TR]
[TD]29039 x 8,module;opto trx sfp 1.25gbe lx ddm.
27424 x 2,card;isa-gbe access/2;1660sm.[/TD]
[/TR]
[TR]
[TD]1660 subrack & cards[/TD]
[/TR]
[TR]
[TD]SAP No 27832[/TD]
[/TR]
[TR]
[TD]SAP.29218 x1.[/TD]
[/TR]
[TR]
[TD]SAP.28956 x1.[/TD]
[/TR]
[TR]
[TD]SAP.22776 x6.[/TD]
[/TR]
[TR]
[TD]25675
CABLE;MA5100 H511CESC TO DDF;120 OHM;30M[/TD]
[/TR]
[TR]
[TD]SAP.24810 x3,24814 x6.[/TD]
[/TR]
[TR]
[TD]sap 24809 x 7[/TD]
[/TR]
[TR]
[TD]sap 21153 x 1[/TD]
[/TR]
[TR]
[TD]sap 27364 x 2[/TD]
[/TR]
[TR]
[TD]sap 24809 x 1[/TD]
[/TR]
[TR]
[TD]SAP.25044 x10,19259 x10,26036 x5.[/TD]
[/TR]
[TR]
[TD]sap 28717 x 1, sap 24810 x 12, sap 27917 x 4[/TD]
[/TR]
[TR]
[TD]SAP.28788 x1,28954 x1,27831 x1,27834 x1,27834 x1,27339 x1,27835 x1,
27839 x1,27840 x2,27502 x4,28085 x1.[/TD]
[/TR]
[TR]
[TD]Cable Management , Waterfall Kit. As per invoice 6 items ordered 6 items
supplied.[/TD]
[/TR]
[TR]
[TD]20703 x 2,Module disconn;10 pr in/10 pr out;cat5e[/TD]
[/TR]
[TR]
[TD]24810 x 2,Card;2xoc-48/stm-16;vt1.5/lo sfp,ome6500
24814 x 4,Module;occcc-48/stm-16;sr-1/i-16.1 xct[/TD]
[/TR]
[TR]
[TD]28952 x 2, isa;es16-b;c/w 1 x 3al81915ab or
27424 x 2, card;isa-gbe access/2;1660m[/TD]
[/TR]
[TR]
[TD]27424 x 1,card;isa-gbe access/2;1660sm[/TD]
[/TR]
[TR]
[TD]28952 x 2,isa;es16-b;c/w 1 x 3al81915ab or
27424 x 2,card;isa-gbe access/2;1660sm[/TD]
[/TR]
[TR]
[TD]28952 x 2,isa;es16-b;c/w 1 x 3al81915ab or
27424 x 1,card;isa-gbe access/2;1660sm
27423 x 1,card;isa-ethaccess;1660sm[/TD]
[/TR]
[TR]
[TD]27502 x 4,card;dummy plate w20;22220mm;1660sm[/TD]
[/TR]
[TR]
[TD]22832 x 2,shelf;splice/patch;iru;19in rack mtg[/TD]
[/TR]
[TR]
[TD]SAP.90629 x1,27972 x1,28084 x1,28426 x2,29259 x1,28089 x1,27339 x2,
28956 x1,27338 x1,27341 x2,27364 x4,27363 x1,27502 x17.[/TD]
[/TR]
[TR]
[TD]SAP.25753 x2. [TABLE="width: 1159"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]CONNECTOR;BNC;STR CRIMP PLUG;RA700[/TD]
[/TR]
[TR]
[TD]27423 x 1,Card;isa-eth access;1660sm. 27424 x 1,Card;isa-gbe access/2;
1660sm. 29039 x 1,Module;opto trx sfp 1.25gbe lx ddm.[/TD]
[/TR]
[TR]
[TD]22780 x 1,panel alarm & pwr distr;48vdc; no circuit.
22783 x 10,circuit breaker;1p;20a;240v;6ka;c-curve.[/TD]
[/TR]
[TR]
[TD]29039 x 8,module;opto trx sfp 1.25gbe lx ddm.
27424 x 2,card;isa-gbe access/2;1660sm.[/TD]
[/TR]
[TR]
[TD]1660 subrack & cards[/TD]
[/TR]
[TR]
[TD]SAP No 27832[/TD]
[/TR]
[TR]
[TD]SAP.29218 x1.[/TD]
[/TR]
[TR]
[TD]SAP.28956 x1.[/TD]
[/TR]
[TR]
[TD]SAP.22776 x6.[/TD]
[/TR]
[TR]
[TD]25675
CABLE;MA5100 H511CESC TO DDF;120 OHM;30M[/TD]
[/TR]
[TR]
[TD]SAP.24810 x3,24814 x6.[/TD]
[/TR]
[TR]
[TD]sap 24809 x 7[/TD]
[/TR]
[TR]
[TD]sap 21153 x 1[/TD]
[/TR]
[TR]
[TD]sap 27364 x 2[/TD]
[/TR]
[TR]
[TD]sap 24809 x 1[/TD]
[/TR]
[TR]
[TD]SAP.25044 x10,19259 x10,26036 x5.[/TD]
[/TR]
[TR]
[TD]sap 28717 x 1, sap 24810 x 12, sap 27917 x 4[/TD]
[/TR]
[TR]
[TD]SAP.28788 x1,28954 x1,27831 x1,27834 x1,27834 x1,27339 x1,27835 x1,
27839 x1,27840 x2,27502 x4,28085 x1.[/TD]
[/TR]
[TR]
[TD]Cable Management , Waterfall Kit. As per invoice 6 items ordered 6 items
supplied.[/TD]
[/TR]
[TR]
[TD]20703 x 2,Module disconn;10 pr in/10 pr out;cat5e[/TD]
[/TR]
[TR]
[TD]24810 x 2,Card;2xoc-48/stm-16;vt1.5/lo sfp,ome6500
24814 x 4,Module;occcc-48/stm-16;sr-1/i-16.1 xct[/TD]
[/TR]
[TR]
[TD]28952 x 2, isa;es16-b;c/w 1 x 3al81915ab or
27424 x 2, card;isa-gbe access/2;1660m[/TD]
[/TR]
[TR]
[TD]27424 x 1,card;isa-gbe access/2;1660sm[/TD]
[/TR]
[TR]
[TD]28952 x 2,isa;es16-b;c/w 1 x 3al81915ab or
27424 x 2,card;isa-gbe access/2;1660sm[/TD]
[/TR]
[TR]
[TD]28952 x 2,isa;es16-b;c/w 1 x 3al81915ab or
27424 x 1,card;isa-gbe access/2;1660sm
27423 x 1,card;isa-ethaccess;1660sm[/TD]
[/TR]
[TR]
[TD]27502 x 4,card;dummy plate w20;22220mm;1660sm[/TD]
[/TR]
[TR]
[TD]22832 x 2,shelf;splice/patch;iru;19in rack mtg[/TD]
[/TR]
[TR]
[TD]SAP.90629 x1,27972 x1,28084 x1,28426 x2,29259 x1,28089 x1,27339 x2,
28956 x1,27338 x1,27341 x2,27364 x4,27363 x1,27502 x17.[/TD]
[/TR]
[TR]
[TD]SAP.25753 x2.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top