robredo101
New Member
- Joined
- Jun 4, 2014
- Messages
- 1
Hi guys,
Wondering if you guys would be able to provide some assistance with this..
<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>
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.
<TBODY> </TBODY><COLGROUP><COL></COLGROUP> |
<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>