Automation to add 99 before any 10 digits?

[Raja]

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

 

[Nigel]

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

 

[Pecoflyer]

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 unnecessar
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 anytim

--
Pecoflye

Cheers -

'Firefox 3.5' (http://www.mozilla.com/en-US/firefox/all-beta.html
really IS fast

 

[Patrick Molloy]

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

 

[Rick Rothstein]

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")