On Sat, 11 Jul 2009 10:54:37 +0100, Raja
wrote:


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

Try this macro

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
If Len(Target.Value) < 12 And Target.Value 0 Then
Target.Value = Left("990000000000", 12 - Len(Target.Value)) &
Target.Value
End If
End If
End Sub

You don't say what to do with numbers with 11 or less than 8 digits,
but the same scheme is applied for them as for the 8,9, and 10 digit
numbers.
Change the range "A1:B10" to suit your needs.
You may also want to apply a custom number format, like 000000000000,
to the cells with these serial numbers.

Hope this helps / Lars-Åke