Seperating Cells With A Mixture Of Text/Numbers By 2 Each Tme

[Dazzawm]

I have attached a sheet with 2 examples of what I need to achieve. What I need is for the code to concentrate on the catcode (column C). It needs to put a gap of 2 between the digits at the end of each cat code. Some may be in straight numerical order like the first example, some may have the end digits going from 001, 005, 012 etc etc.. like in the second example. Is this possible to be done?

Data also stretches across to column AI but I have not bothered including these in the examples as I dont think it is necessary, as no rows need to be copied or removed, just the cells in column C need changing. Thanks.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 69px"><COL style="WIDTH: 48px"><COL style="WIDTH: 97px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Index</TD><TD>MVRIS</TD><TD>CatCode</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Example 1</TD><TD>Before</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM72828 001</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM72828 001</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM72828 002</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM72828 002</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM72828 003</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM72828 003</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM72828 004</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM72828 004</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD>Example 1</TD><TD>After</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM72828 001</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM72828 001</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM72828 003</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM72828 003</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM72828 005</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM72828 005</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM72828 007</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM72828 007</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD>Example 2</TD><TD>Before</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 001</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 001</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 001</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 001</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 001</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 001</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 003</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 003</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">33</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 003</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">34</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 004</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">35</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 004</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">36</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 004</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">37</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 005</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">38</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 005</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">39</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 005</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">40</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 006</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">41</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 006</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">42</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 006</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">43</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 006</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">44</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 006</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">45</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 006</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">46</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 006</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">47</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 006</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">48</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 006</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">49</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 006</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">50</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 006</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">51</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 006</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">52</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">53</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">54</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">55</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">56</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">57</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">58</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">59</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">60</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">61</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">62</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">63</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">64</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 012</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">65</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 012</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">66</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 012</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">67</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 012</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">68</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 012</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">69</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 012</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">70</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 012</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">71</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 012</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">72</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 012</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">73</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 014</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">74</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 014</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">75</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 014</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">76</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 014</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">77</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 014</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">78</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 014</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">79</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">80</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">81</TD><TD>Example 2</TD><TD>After</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">82</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 001</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">83</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 001</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">84</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 001</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">85</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 001</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">86</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 001</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">87</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 001</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">88</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 003</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">89</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 003</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">90</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 003</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">91</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 005</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">92</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 005</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">93</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 005</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">94</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 007</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">95</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 007</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">96</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 007</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">97</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">98</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">99</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">100</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">101</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">102</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">103</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">104</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">105</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">106</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">107</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">108</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 009</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">109</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">110</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">111</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">112</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">113</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">114</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">115</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">116</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">117</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">118</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">119</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">120</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">121</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 013</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">122</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 013</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">123</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 013</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">124</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 013</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">125</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 013</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">126</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 013</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">127</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 013</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">128</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 013</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">129</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 013</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">130</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 015</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">131</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 015</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">132</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 015</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">133</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 015</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">134</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 015</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">135</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>BM32020D 015</TD></TR></TBODY></TABLE>

 

[Dazzawm]

I dont need the data in F changed I need it to stay the same. I have put what I need done to it for when it is put in column C (held within a variable I am guessing).

 

[MickG]

Two things the second element ( AR147_19D) appears to be the same as the first element, Yet in the "After" column you still show it, still with the last letter "D", and if they are the same , then in the final list, the second element should have the suffix od "0004", or have I read it wrong ??
Also if the Number increases by 4 then the second number should be "0005", or have I read that wrong as well.
If you show a list of 3 or 4 increments ie "0001", "0004" etc I'll get thr drift !!!!

 

[Dazzawm]

I am sorry, my mistake. There will be only 2 letters at the end A or D. The D must remain the A must go. Ideally I need the numbers to increase by the following 1001, 1004, 1008, 1011, 1015 which is really 3 then 4, 3 then 4 etc... I hope this doesn't complicate things if so then 3 at a time 1001, 1004, 1007, 1010 etc.. Thanks. Let me know if anything else needs clarifying. Did you see post 21?

Another mistake also I noticed is I put 0001 etc instead of it starting with a 1 i.e 1001 etc.

Each file I use this on the first number will increase by 1 so the second file will be 2001, 2004 etc.. so if you could point out what I would need to change each time I would be very grateful.

 

[Dazzawm]

Hopefully this will make it clearer.

Excel Workbook
	C	D	E	F
54	AR3315 1001			AR33__15A
55	AR3315 1004			AR33__15A
56	AR3315 1008			AR33__15A
57	AR3315 1011			AR33__15A
58	AR3317 1001			AR33__17A
59	AR3317 1004			AR33__17A
60	AR3317 1008			AR33__17A
61	AR3317 1011			AR33__17A
62	ARALF13 1001			ARALF_13A
63	ARALF13 1004			ARALF_13A
64	ARALF15 1001			ARALF_15A
65	ARALF15 1004			ARALF_15A
66	ARALF15 1008			ARALF_15A
67	ARARN12 1001			ARARN_12A
68	ARARN13 1001			ARARN_13A
69	ARARN15 1001			ARARN_15A
70	ARGIU14 1001			ARGIU_14A
71	ARGIU14 1003			ARGIU_14A
72	ARGIU16D 1001			ARGIU_16D
73	ARGIU18 1001			ARGIU_18A
74	ARGIU20D 1001			ARGIU_20D
75	ARGIU20D 1004			ARGIU_20D
76	ARGIU20D 1008			ARGIU_20D
77	ARGIU20D 1011			ARGIU_20D
78	ARGIU20D 1014			ARGIU_20D
Hat (1000)

 

[MickG]

Try this:-<BR>Data in "F" Results in "C"<BR>

<BR><FONT color=navy>Sub</FONT> MG03Sep27<BR><FONT color=navy>Dim</FONT> Rng <FONT color=navy>As</FONT> Range<BR><FONT color=navy>Dim</FONT> Dn <FONT color=navy>As</FONT> Range<BR><FONT color=navy>Dim</FONT> oStr <FONT color=navy>As</FONT> <FONT color=navy>String</FONT><BR><FONT color=navy>Dim</FONT> Q<BR><FONT color=navy>Set</FONT> Rng = Range(Range("F1"), Range("F" & Rows.count).End(xlUp))<BR><FONT color=navy>For</FONT> <FONT color=navy>Each</FONT> Dn <FONT color=navy>In</FONT> Rng<BR>    Dn.Offset(, -3) = IIf(Right(Dn, 1) = "A", Left(Replace(Dn, "_", ""), Len(Replace(Dn, "_", "")) - 1), Replace(Dn, "_", ""))<BR><FONT color=navy>Next</FONT> Dn<BR><FONT color=navy>Set</FONT> Rng = Rng.Offset(, -3)<BR><FONT color=navy>With</FONT> CreateObject("scripting.dictionary")<BR>.CompareMode = vbTextCompare<BR><FONT color=navy>For</FONT> <FONT color=navy>Each</FONT> Dn <FONT color=navy>In</FONT> Rng<BR>    <FONT color=navy>If</FONT> Not .Exists(Dn.Value) <FONT color=navy>Then</FONT><BR>        .Add Dn.Value, 1001<BR>            Dn = Dn & 1001<BR>    <FONT color=navy>Else</FONT><BR>        .Item(Dn.Value) = IIf(.Item(Dn.Value) = 1001, .Item(Dn.Value) + 3, .Item(Dn.Value) + 4)<BR>        Dn = Dn & .Item(Dn.Value)<BR>    <FONT color=navy>End</FONT> If<BR><FONT color=navy>Next</FONT><BR><FONT color=navy>End</FONT> With<BR><FONT color=navy>End</FONT> <FONT color=navy>Sub</FONT><BR>

<BR>Regards Mick

 

[Dazzawm]

I have copied the code into the file but a lot of it is in red?

 

[Dazzawm]

Ok I have got it to work. One thing though. It works ok but it doesn't put a gap between the data and the numbers i.e it is like this ANMAE131001 instead of ANMAE13 1001.

 

[Dazzawm]

I got it to work, so I am guessing I got it right. I changed below.


Dn = Dn & 1001 to Dn = Dn & " " & 1001

Dn = Dn & .Item(Dn.Value) to Dn = Dn & " " & .Item(Dn.Value)

Thanks for all your help Mick you are a legend.

 

[MickG]

I thought I'd scrapped the Previous code, Please Ignore !!

That previous code should have not given you the right Number increment !!!!
Try this:-

[COLOR=navy]Sub[/COLOR] MG03Sep59
[COLOR=navy]Dim[/COLOR] Rng         [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn          [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] oStr        [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] n           [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Q
[COLOR=navy]Dim[/COLOR] fd          [COLOR=navy]As[/COLOR] Boolean
[COLOR=navy]Set[/COLOR] Rng = Range(Range("F1"), Range("F" & Rows.count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    Dn.Offset(, -3) = IIf(Right(Dn, 1) = "A", Left(Replace(Dn, "_", ""), Len(Replace(Dn, "_", "")) - 1), Replace(Dn, "_", ""))
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]Set[/COLOR] Rng = Rng.Offset(, -3)
    ReDim Ray(1 To Rng.count)
        [COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=navy]Then[/COLOR]
        n = n + 1
        .Add Dn.Value, Array(1001, fd)
           Ray(Dn.row) = Dn & " " & 1001
    [COLOR=navy]Else[/COLOR]
        Q = .Item(Dn.Value)
            Q(1) = Q(1) Xor True
            Q(0) = IIf(Q(1) = True, Q(0) + 3, Q(0) + 4)
            Ray(Dn.row) = Dn.Value & " " & Q(0)
       .Item(Dn.Value) = Q
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
[COLOR=navy]End[/COLOR] With
Range("C1").Resize(Rng.count) = application.Transpose(Ray)
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Dazzawm]

I thought I'd scrapped the Previous code, Please Ignore !!

That previous code should have not given you the right Number increment !!!!
Regards Mick

Oh I didnt notice, I see now it when 4 up at atime rather than 3 then 4.