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>

 

[Special-K99]

If I've understood this correctly

you want
BM72828space001
changed to
BM72828spacespace001

If thats right try this

Insert a column after column C

in D3
=IF(C3<>"",LEFT(C3,LEN(C3)-3)&CODE(10)&RIGHT(C3,3),"")

Copy column D
Paste Values back over column D
Delete column C

Also see here
http://www.dailydoseofexcel.com/archives/2004/10/19/line-breaks-in-formulas/

 

[Dazzawm]

Thanks Special-K99 but you are way off track!! The last 3 digits need to increase by 2 every time e.g not 1 2 3 4 but 1 3 5 7

 

[Rick Rothstein]

Does this macro do what you want?

Sub InsertTwoRowsAtCatCodeChange()
  Dim X As Long, LastRow As Long, UnusedColumn As Long, Ar As Range
  Const CatCodeCol As Long = 3  'Column C
  Const StartRow As Long = 3
  LastRow = Cells(Rows.Count, CatCodeCol).End(xlUp).Row
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  With Range(Cells(StartRow, UnusedColumn), Cells(LastRow, UnusedColumn))
    .FormulaR1C1 = "=IF(RIGHT(RC" & CatCodeCol & ",3)<=RIGHT(R[-1]C" & CatCodeCol & ",3),""X"","""")"
    .Value = .Value
    For Each Ar In .SpecialCells(xlConstants).Areas
      For X = Ar.Count To 1 Step -1
        Ar(X).Resize(2).EntireRow.Insert
      Next
    Next
    .Clear
  End With
End Sub

 

[Dazzawm]

No thats not what I need either. I dont need rows inserted or spaces. I need the last 3 digits on each cell changed so that there is a 2 digit gap like in the before/after examples. So instead of the last 3 being 001, 002, 003, 004 it needs to be 001 then 003 then 005 then 007 etc etc

Its very hard to explain I know!!

I guess the code firstly needs to look at the data before the space then the numbers after so when the cells change 2 needs adding, then when the data before the space changes the end digits need to start 001 and so on. I thought the examples would show what I needed as its hard to explain

 

[MickG]

Try this:-
NB:- Results will overwrite data in Column "C". (Be aware)

[COLOR="Navy"]Sub[/COLOR] MG17Jan48
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dic1        [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Dic2        [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] St          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ar          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] A           [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] K1, K2
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C3"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Dic1 = CreateObject("scripting.dictionary")
Dic1.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dic1.Exists(Split(Dn.Value, " ")(0)) [COLOR="Navy"]Then[/COLOR]
        Dic1.Add Split(Dn.Value, " ")(0), Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] Dic1.Item(Split(Dn.Value, " ")(0)) = Union(Dic1.Item(Split(Dn.Value, " ")(0)), Dn)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K1 [COLOR="Navy"]In[/COLOR] Dic1.Keys
    [COLOR="Navy"]Set[/COLOR] Dic2 = CreateObject("scripting.dictionary")
        Dic2.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ar [COLOR="Navy"]In[/COLOR] Dic1.Item(K1).Areas
  [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] A [COLOR="Navy"]In[/COLOR] Ar
        [COLOR="Navy"]If[/COLOR] Not Dic2.Exists(A.Value) [COLOR="Navy"]Then[/COLOR]
            Dic2.Add A.Value, A
         [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] Dic2.Item(A.Value) = Union(Dic2.Item(A.Value), A)
        [COLOR="Navy"]End[/COLOR] If
  [COLOR="Navy"]Next[/COLOR] A
[COLOR="Navy"]Next[/COLOR] Ar
n = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K2 [COLOR="Navy"]In[/COLOR] Dic2.Keys
    [COLOR="Navy"]If[/COLOR] n < 10 [COLOR="Navy"]Then[/COLOR]
        St = "00"
    [COLOR="Navy"]ElseIf[/COLOR] n < 99 [COLOR="Navy"]Then[/COLOR]
        St = "0"
    [COLOR="Navy"]End[/COLOR] If
Dic2.Item(K2).value = K1 & " " & St & n
n = n + 2
[COLOR="Navy"]Next[/COLOR] K2
[COLOR="Navy"]Next[/COLOR] K1
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Dazzawm]

Wow MickG thanks a lot that seems to do exactly what I want. I will try on a big file tomorrow annd let you know.

 

[Rick Rothstein]

Wow MickG thanks a lot that seems to do exactly what I want. I will try on a big file tomorrow annd let you know.

Here is another macro (a little shorter than Mick's) which I think will also work for you...

Sub SkipByTwos()
  Dim X As Long, LastRow As Long, NewLastFour As String, OldLastFour As String
  Const DataCol As String = "C"
  Const StartRow As Long = 3
  LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
  Columns(DataCol).Replace " ", Chr(1), xlPart
  OldLastFour = Right(Cells(StartRow, DataCol).Value, 4)
  NewLastFour = " 001"
  For X = StartRow To LastRow
    If Right(Cells(X, DataCol), 4) <> OldLastFour Then
      OldLastFour = Right(Cells(X, DataCol), 4)
      NewLastFour = Format(NewLastFour + 2, " 000")
    End If
    Cells(X, DataCol).Value = WorksheetFunction.Replace(Cells(X, DataCol).Value, Len(Cells(X, DataCol).Value) - 3, 4, NewLastFour)
  Next
End Sub

NOTE: I was not clear as to where your data started at, so I included a constant (the Const statement) that you could set it in (I defaulted it to 3 because that is where your data started on your posted example). Also, I assume there is only one run of data in Column C (not the two you showed in for example purposes).

 

[Dazzawm]

Thanks Rick yours also does what I am after. The difference between yours and Micks is that your code adds 2 on to the data from the very first number e.g a cell may start 011 so your code continues 013, 015 etc whereas Micks will look at the cell starting 011 but change it to start at 001 then 003, 005 etc, which is exactly what I am after (although I did not say this in my post). Thanks so much to both of you for your help.

 

[Rick Rothstein]

Thanks Rick yours also does what I am after. The difference between yours and Micks is that your code adds 2 on to the data from the very first number e.g a cell may start 011 so your code continues 013, 015 etc whereas Micks will look at the cell starting 011 but change it to start at 001 then 003, 005 etc, which is exactly what I am after (although I did not say this in my post). Thanks so much to both of you for your help.

That is not what happens when I run my code here... no matter what the last three numbers in the first cell (C3) is, my code make them 001 and then continues on from there. Are you saying that you see it behave differently? The best I can tell, Mick and my code produce the same final results.