adjusteing numbers in a column

aminexcel

Board Regular
Joined
May 2, 2009
Messages
63
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>
 
Last edited:

RolfJ

Banned
Joined
Jul 29, 2009
Messages
333
The formula you could use in column2 of your table 1 is:

Code:
=IF(A1<10,CONCATENATE("0",A1),A1)
It is not clear to me what you intend to do in table2.

Best wishes,
Rolf
 

wsjackman

Well-known Member
Joined
Jun 19, 2008
Messages
2,465
Excel Workbook
ABCDEFGHI
1803406004060000406000000040600040600000406000000
28045040250402005040200000804050402050402000504020000
38056020260202025040200000805060202060202020504020000
48066030060300006020202000806060300060300000602020200
5809701031203020312030203000809070103120302031203020300
612031203021302040013020400001203120302130204001302040000
713021302045802010214050100001302130204580201021405010000
814051405015802090214050100001405140501580209021405010000
915071405025804050214050200001507140502580405021405020000
1015171507105809050014050200201517150710580905001405020020
Sheet12
 
Last edited:

aminexcel

Board Regular
Joined
May 2, 2009
Messages
63
i want to use vlookup and left functions in columns

thanks a lot
i solved it

sincerely :)
 

Forum statistics

Threads
1,082,115
Messages
5,363,247
Members
400,722
Latest member
DrewPop24

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top