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

[robredo101]

Hi guys,

Wondering if you guys would be able to provide some assistance with this..

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)

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

CONNECTOR;BNC;STR CRIMP PLUG;RA700</SPAN>

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>

22780 x 1,panel alarm & pwr distr;48vdc; no circuit. 22783 x 10,circuit breaker;1p;20a;240v;6ka;c-curve.</SPAN>

29039 x 8,module;opto trx sfp 1.25gbe lx ddm. 27424 x 2,card;isa-gbe access/2;1660sm.</SPAN>

1660 subrack & cards</SPAN>

SAP No 27832</SPAN>

SAP.29218 x1.</SPAN>

SAP.28956 x1.</SPAN>

SAP.22776 x6.</SPAN>

25675 CABLE;MA5100 H511CESC TO DDF;120 OHM;30M</SPAN>

SAP.24810 x3,24814 x6.</SPAN>

sap 24809 x 7</SPAN>

sap 21153 x 1</SPAN>

sap 27364 x 2</SPAN>

sap 24809 x 1</SPAN>

SAP.25044 x10,19259 x10,26036 x5.</SPAN>

sap 28717 x 1, sap 24810 x 12, sap 27917 x 4</SPAN>

SAP.28788 x1,28954 x1,27831 x1,27834 x1,27834 x1,27339 x1,27835 x1, 27839 x1,27840 x2,27502 x4,28085 x1.</SPAN>

Cable Management , Waterfall Kit. As per invoice 6 items ordered 6 items supplied.</SPAN>

20703 x 2,Module disconn;10 pr in/10 pr out;cat5e</SPAN>

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>

28952 x 2, isa;es16-b;c/w 1 x 3al81915ab or 27424 x 2, card;isa-gbe access/2;1660m</SPAN>

27424 x 1,card;isa-gbe access/2;1660sm</SPAN>

28952 x 2,isa;es16-b;c/w 1 x 3al81915ab or 27424 x 2,card;isa-gbe access/2;1660sm</SPAN>

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>

27502 x 4,card;dummy plate w20;22220mm;1660sm</SPAN>

22832 x 2,shelf;splice/patch;iru;19in rack mtg</SPAN>

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>

SAP.25753 x2. </SPAN> </SPAN> CONNECTOR;BNC;STR CRIMP PLUG;RA700</SPAN> 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> 22780 x 1,panel alarm & pwr distr;48vdc; no circuit. 22783 x 10,circuit breaker;1p;20a;240v;6ka;c-curve.</SPAN> 29039 x 8,module;opto trx sfp 1.25gbe lx ddm. 27424 x 2,card;isa-gbe access/2;1660sm.</SPAN> 1660 subrack & cards</SPAN> SAP No 27832</SPAN> SAP.29218 x1.</SPAN> SAP.28956 x1.</SPAN> SAP.22776 x6.</SPAN> 25675 CABLE;MA5100 H511CESC TO DDF;120 OHM;30M</SPAN> SAP.24810 x3,24814 x6.</SPAN> sap 24809 x 7</SPAN> sap 21153 x 1</SPAN> sap 27364 x 2</SPAN> sap 24809 x 1</SPAN> SAP.25044 x10,19259 x10,26036 x5.</SPAN> sap 28717 x 1, sap 24810 x 12, sap 27917 x 4</SPAN> SAP.28788 x1,28954 x1,27831 x1,27834 x1,27834 x1,27339 x1,27835 x1, 27839 x1,27840 x2,27502 x4,28085 x1.</SPAN> Cable Management , Waterfall Kit. As per invoice 6 items ordered 6 items supplied.</SPAN> 20703 x 2,Module disconn;10 pr in/10 pr out;cat5e</SPAN> 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> 28952 x 2, isa;es16-b;c/w 1 x 3al81915ab or 27424 x 2, card;isa-gbe access/2;1660m</SPAN> 27424 x 1,card;isa-gbe access/2;1660sm</SPAN> 28952 x 2,isa;es16-b;c/w 1 x 3al81915ab or 27424 x 2,card;isa-gbe access/2;1660sm</SPAN> 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> 27502 x 4,card;dummy plate w20;22220mm;1660sm</SPAN> 22832 x 2,shelf;splice/patch;iru;19in rack mtg</SPAN> 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> SAP.25753 x2.</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP> </SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[Michael M]

Maybe something like this ...a VBA solution
Previously posted by apo from Wirribi

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

 

[rachelmchugh]

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

Hi guys,

Wondering if you guys would be able to provide some assistance with this..

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)

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

CONNECTOR;BNC;STR CRIMP PLUG;RA700

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.

22780 x 1,panel alarm & pwr distr;48vdc; no circuit. 22783 x 10,circuit breaker;1p;20a;240v;6ka;c-curve.

29039 x 8,module;opto trx sfp 1.25gbe lx ddm. 27424 x 2,card;isa-gbe access/2;1660sm.

1660 subrack & cards

SAP No 27832

SAP.29218 x1.

SAP.28956 x1.

SAP.22776 x6.

25675 CABLE;MA5100 H511CESC TO DDF;120 OHM;30M

SAP.24810 x3,24814 x6.

sap 24809 x 7

sap 21153 x 1

sap 27364 x 2

sap 24809 x 1

SAP.25044 x10,19259 x10,26036 x5.

sap 28717 x 1, sap 24810 x 12, sap 27917 x 4

SAP.28788 x1,28954 x1,27831 x1,27834 x1,27834 x1,27339 x1,27835 x1, 27839 x1,27840 x2,27502 x4,28085 x1.

Cable Management , Waterfall Kit. As per invoice 6 items ordered 6 items supplied.

20703 x 2,Module disconn;10 pr in/10 pr out;cat5e

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

28952 x 2, isa;es16-b;c/w 1 x 3al81915ab or 27424 x 2, card;isa-gbe access/2;1660m

27424 x 1,card;isa-gbe access/2;1660sm

28952 x 2,isa;es16-b;c/w 1 x 3al81915ab or 27424 x 2,card;isa-gbe access/2;1660sm

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

27502 x 4,card;dummy plate w20;22220mm;1660sm

22832 x 2,shelf;splice/patch;iru;19in rack mtg

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.

SAP.25753 x2. CONNECTOR;BNC;STR CRIMP PLUG;RA700 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. 22780 x 1,panel alarm & pwr distr;48vdc; no circuit. 22783 x 10,circuit breaker;1p;20a;240v;6ka;c-curve. 29039 x 8,module;opto trx sfp 1.25gbe lx ddm. 27424 x 2,card;isa-gbe access/2;1660sm. 1660 subrack & cards SAP No 27832 SAP.29218 x1. SAP.28956 x1. SAP.22776 x6. 25675 CABLE;MA5100 H511CESC TO DDF;120 OHM;30M SAP.24810 x3,24814 x6. sap 24809 x 7 sap 21153 x 1 sap 27364 x 2 sap 24809 x 1 SAP.25044 x10,19259 x10,26036 x5. sap 28717 x 1, sap 24810 x 12, sap 27917 x 4 SAP.28788 x1,28954 x1,27831 x1,27834 x1,27834 x1,27339 x1,27835 x1, 27839 x1,27840 x2,27502 x4,28085 x1. Cable Management , Waterfall Kit. As per invoice 6 items ordered 6 items supplied. 20703 x 2,Module disconn;10 pr in/10 pr out;cat5e 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 28952 x 2, isa;es16-b;c/w 1 x 3al81915ab or 27424 x 2, card;isa-gbe access/2;1660m 27424 x 1,card;isa-gbe access/2;1660sm 28952 x 2,isa;es16-b;c/w 1 x 3al81915ab or 27424 x 2,card;isa-gbe access/2;1660sm 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 27502 x 4,card;dummy plate w20;22220mm;1660sm 22832 x 2,shelf;splice/patch;iru;19in rack mtg 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. SAP.25753 x2. <tbody> </tbody>

<tbody>
</tbody>