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

Dazzawm

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,783
Office Version
  1. 365
Platform
  1. Windows
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>
 
Last edited:
Sort by date Sort by votes
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).
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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 !!!!
 
Upvote 0
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.
 
Upvote 0
Hopefully this will make it clearer.

Excel Workbook
CDEF
54AR3315 1001AR33__15A
55AR3315 1004AR33__15A
56AR3315 1008AR33__15A
57AR3315 1011AR33__15A
58AR3317 1001AR33__17A
59AR3317 1004AR33__17A
60AR3317 1008AR33__17A
61AR3317 1011AR33__17A
62ARALF13 1001ARALF_13A
63ARALF13 1004ARALF_13A
64ARALF15 1001ARALF_15A
65ARALF15 1004ARALF_15A
66ARALF15 1008ARALF_15A
67ARARN12 1001ARARN_12A
68ARARN13 1001ARARN_13A
69ARARN15 1001ARARN_15A
70ARGIU14 1001ARGIU_14A
71ARGIU14 1003ARGIU_14A
72ARGIU16D 1001ARGIU_16D
73ARGIU18 1001ARGIU_18A
74ARGIU20D 1001ARGIU_20D
75ARGIU20D 1004ARGIU_20D
76ARGIU20D 1008ARGIU_20D
77ARGIU20D 1011ARGIU_20D
78ARGIU20D 1014ARGIU_20D
Hat (1000)
 
Upvote 0
Try this:-<BR>Data in "F" Results in "C"<BR>
Code: 
<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
 
Upvote 0
I have copied the code into the file but a lot of it is in red?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
I thought I'd scrapped the Previous code, Please Ignore !!

That previous code should have not given you the right Number increment !!!!
Try this:-
Code: 
[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
 
Last edited:
Upvote 0
MickG said:
I thought I'd scrapped the Previous code, Please Ignore !!

That previous code should have not given you the right Number increment !!!!
Regards Mick
Click to expand...

Oh I didnt notice, I see now it when 4 up at atime rather than 3 then 4.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
570
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
536
mumps
mumps
B
  • Solved
Do I need a Vlookup on steriods?
Replies
4
Views
130
bumfart66
B
Geek Girl 007
  • Solved
change colour based on specific text.
Replies
5
Views
234
Cubist
Cubist
U
  • Solved
comparing 2 different columns and looking for missing numbers
Replies
3
Views
271
Fluff
Fluff

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

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