adjusteing numbers in a column

[aminexcel]

i want a formula or a macro that adjust n and m (m=n-1) digit numbers in a column with adding a zero (0) before m digit numbers. fore example make column2 from column1 in table1. i want this formula or macro do it without selecting cells because i have 250 worksheets and can not do it separately for every worksheets. i have 1&2 (A column), 3&4 (B column), 5&6 (C column), 7&8 (D column), 9&10 (E column) digits in separate columns (table2). please note that all numbers are in text formats
sincerely yours
many thanks
amin

table1
<table style="border-collapse: collapse; width: 108pt;" border="0" cellpadding="0" cellspacing="0" width="144"><col style="width: 54pt;" span="2" width="72"> <tbody><tr style="height: 14.25pt;" height="19"> <td class="xl64" style="height: 14.25pt; width: 54pt;" height="19" width="72">column1</td> <td style="width: 54pt;" align="left" width="72">column2</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" style="height: 14.25pt; width: 54pt;" height="19" width="72">4</td> <td class="xl65" style="border-left: medium none; width: 54pt;" width="72">04</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" style="border-top: medium none; height: 14.25pt; width: 54pt;" height="19" width="72">5</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 54pt;" width="72">05</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" style="border-top: medium none; height: 14.25pt; width: 54pt;" height="19" width="72">6</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 54pt;" width="72">06</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" style="border-top: medium none; height: 14.25pt; width: 54pt;" height="19" width="72">7</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 54pt;" width="72">07</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" style="border-top: medium none; height: 14.25pt; width: 54pt;" height="19" width="72">7</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 54pt;" width="72">08</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" style="border-top: medium none; height: 14.25pt; width: 54pt;" height="19" width="72">9</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 54pt;" width="72">09</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" style="border-top: medium none; height: 14.25pt; width: 54pt;" height="19" width="72">12</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 54pt;" width="72">12</td> </tr> </tbody></table>
table2
<table style="border-collapse: collapse; width: 205pt;" border="0" cellpadding="0" cellspacing="0" width="273"><col style="width: 22pt;" width="29"> <col style="width: 26pt;" width="35"> <col style="width: 37pt;" width="49"> <col style="width: 47pt;" width="63"> <col style="width: 73pt;" width="97"> <tbody><tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; width: 22pt;" height="19" width="29">A</td> <td class="xl67" style="border-left: medium none; width: 26pt;" width="35">B</td> <td class="xl67" style="border-left: medium none; width: 37pt;" width="49">C</td> <td class="xl67" style="border-left: medium none; width: 47pt;" width="63">D</td> <td class="xl67" style="border-left: medium none; width: 73pt;" width="97">E</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl66" style="border-top: medium none; height: 14.25pt; width: 22pt;" height="19" width="29">4</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 26pt;" width="35">803</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 37pt;" width="49">40600</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 47pt;" width="63">4060000</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 73pt;" width="97">406000000</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl66" style="border-top: medium none; height: 14.25pt; width: 22pt;" height="19" width="29">5</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 26pt;" width="35">804</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 37pt;" width="49">50402</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 47pt;" width="63">5040200</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 73pt;" width="97">504020000</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl66" style="border-top: medium none; height: 14.25pt; width: 22pt;" height="19" width="29">6</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 26pt;" width="35">805</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 37pt;" width="49">60202</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 47pt;" width="63">6020202</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 73pt;" width="97">504020000</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl66" style="border-top: medium none; height: 14.25pt; width: 22pt;" height="19" width="29">7</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 26pt;" width="35">806</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 37pt;" width="49">60300</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 47pt;" width="63">6030000</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 73pt;" width="97">602020200</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl66" style="border-top: medium none; height: 14.25pt; width: 22pt;" height="19" width="29">8</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 26pt;" width="35">809</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 37pt;" width="49">70103</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 47pt;" width="63">12030203</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 73pt;" width="97">1203020300</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl66" style="border-top: medium none; height: 14.25pt; width: 22pt;" height="19" width="29">9</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 26pt;" width="35">1203</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 37pt;" width="49">120302</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 47pt;" width="63">13020400</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 73pt;" width="97">1302040000</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl66" style="border-top: medium none; height: 14.25pt; width: 22pt;" height="19" width="29">12</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 26pt;" width="35">1302</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 37pt;" width="49">130204</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 47pt;" width="63">58020102</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 73pt;" width="97">1405010000</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl66" style="border-top: medium none; height: 14.25pt; width: 22pt;" height="19" width="29">13</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 26pt;" width="35">1405</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 37pt;" width="49">140501</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 47pt;" width="63">58020902</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 73pt;" width="97">1405010000</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl66" style="border-top: medium none; height: 14.25pt; width: 22pt;" height="19" width="29">14</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 26pt;" width="35">1507</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 37pt;" width="49">140502</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 47pt;" width="63">58040502</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 73pt;" width="97">1405020000</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl66" style="border-top: medium none; height: 14.25pt; width: 22pt;" height="19" width="29">15</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 26pt;" width="35">1517</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 37pt;" width="49">150710</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 47pt;" width="63">58090500</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 73pt;" width="97">1405020020</td> </tr> </tbody></table>

 

[RolfJ]

The formula you could use in column2 of your table 1 is:

=IF(A1<10,CONCATENATE("0",A1),A1)

It is not clear to me what you intend to do in table2.

Best wishes,
Rolf

 

[wsjackman]

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	803	40600	4060000	406000000		0803	040600	04060000	0406000000
2	804	50402	5040200	504020000		0804	050402	05040200	0504020000
3	805	60202	6020202	504020000		0805	060202	06020202	0504020000
4	806	60300	6030000	602020200		0806	060300	06030000	0602020200
5	809	70103	12030203	1203020300		0809	070103	12030203	1203020300
6	1203	120302	13020400	1302040000		1203	120302	13020400	1302040000
7	1302	130204	58020102	1405010000		1302	130204	58020102	1405010000
8	1405	140501	58020902	1405010000		1405	140501	58020902	1405010000
9	1507	140502	58040502	1405020000		1507	140502	58040502	1405020000
10	1517	150710	58090500	1405020020		1517	150710	58090500	1405020020
Sheet12

 

[aminexcel]

i want to use vlookup and left functions in columns

thanks a lot
i solved it

sincerely