Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation to add 99 before any 10 digits?
Hi all, Greeting for the day. I have serial number in a column like 0123456789 that is 10 digits however it has to 990123456789 The actual requirement is when i copy paste any 10 digits in a cell say A1 it should add 99 before 10 digits automatically to make it 12 digits. And if it is 9 digit it should add 990 before the 9 digits if it is 8 digit it should add 9900. Any help would be much appreciated. Thanks in Advance Raja -- Raja ------------------------------------------------------------------------ Raja's Profile: http://www.thecodecage.com/forumz/member.php?userid=497 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115081 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation to add 99 before any 10 digits?
Hi Raja, welcome to the board. Please do not post in different forums. Crossposting ( as 'this link' (http://www.excelguru.ca/node/7) describes) gives people unnecessary work. Remember everybody here is a volunteer. I'll close this thread if you don't need a VBA solution. Otherwise, let me know, I'll close the other one. You can PM me or send a visitor's message at anytime -- Pecoflyer Cheers - 'Firefox 3.5' (http://www.mozilla.com/en-US/firefox/all-beta.html) really IS fast ! ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115081 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation to add 99 before any 10 digits?
you can use
(1) a sheet function =LEFT("990000000000",12 - len(A1) ) & A1 so if A1 is 10 digits, 12-10=2, so only the left 2 digits, 99, get prepended if length of A1 is 5, then 12-5 = 7, so lwft 7 digits, 9900000 , get prepended (2) VBA dim cell as range dim text as string set cell = range("A1") do until isempty(cell) text = cell.value cell.value = LEFT("990000000000",12 - len(text) ) & text set cell = cell.offset(1) loop "Raja" wrote in message ... Hi all, Greeting for the day. I have serial number in a column like 0123456789 that is 10 digits however it has to 990123456789 The actual requirement is when i copy paste any 10 digits in a cell say A1 it should add 99 before 10 digits automatically to make it 12 digits. And if it is 9 digit it should add 990 before the 9 digits if it is 8 digit it should add 9900. Any help would be much appreciated. Thanks in Advance Raja -- Raja ------------------------------------------------------------------------ Raja's Profile: http://www.thecodecage.com/forumz/member.php?userid=497 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115081 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation to add 99 before any 10 digits?
There are simple constructions to form these numbers than what you posted...
=LEFT("990000000000",12 - len(A1) ) & A1 =TEXT(A1,"990000000000") cell.value = LEFT("990000000000",12 - len(text) ) & text cell.value = Format(text, "990000000000") -- Rick (MVP - Excel) "Patrick Molloy" wrote in message ... you can use (1) a sheet function =LEFT("990000000000",12 - len(A1) ) & A1 so if A1 is 10 digits, 12-10=2, so only the left 2 digits, 99, get prepended if length of A1 is 5, then 12-5 = 7, so lwft 7 digits, 9900000 , get prepended (2) VBA dim cell as range dim text as string set cell = range("A1") do until isempty(cell) text = cell.value cell.value = LEFT("990000000000",12 - len(text) ) & text set cell = cell.offset(1) loop "Raja" wrote in message ... Hi all, Greeting for the day. I have serial number in a column like 0123456789 that is 10 digits however it has to 990123456789 The actual requirement is when i copy paste any 10 digits in a cell say A1 it should add 99 before 10 digits automatically to make it 12 digits. And if it is 9 digit it should add 990 before the 9 digits if it is 8 digit it should add 9900. Any help would be much appreciated. Thanks in Advance Raja -- Raja ------------------------------------------------------------------------ Raja's Profile: http://www.thecodecage.com/forumz/member.php?userid=497 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115081 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you change single digits to recognized double digits? | Excel Worksheet Functions | |||
VBA write macro change column with 3 number digits to 4 digits the | Excel Discussion (Misc queries) | |||
number 12 digits to 15 digits | Excel Discussion (Misc queries) | |||
How to customize number to 10 digits including 2 digits after deci | Excel Worksheet Functions | |||
Supressing the ctrl-c and other keys during word automation in automation | Excel Programming |