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

Automation to add 99 before any 10 digits?
 

Place on the worksheet in question code sheet

Private Sub Worksheet_Change(ByVal Target As Range)
If Val(Target) 0 Then
Select Case Len(Trim(Target))
Case Is = 10: Target = "99" & Trim(Target)
Case Is = 9: Target = "990" & Trim(Target)
Case Is = 8: Target = "9900" & Trim(Target)
End Select
End If
End Sub

--

Regards,
Nigel




"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

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

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

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