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=115078

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

Raja;413082 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

A possible formula solution
Code:
--------------------
=LEFT(("990000000000";12-LEN(A1))&A1)
--------------------


Replace ; with , if needed


--
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=115078

Pecoflyer;413095 Wrote:
A possible formula solution
Code:
--------------------
=LEFT(("990000000000";12-LEN(A1))&A1)
--------------------


Replace ; with , if needed

Thanks advice please advice in which cell i hav eto copy paste the
formula


--
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=115078

Raja;413100 Wrote:
Thanks advice please advice in which cell i hav eto copy paste the
formula
--- Automerged consecutive post before response ---


Thanks peocflyer

please advice where i have insert this formula as im new to excel.
--- Automerged consecutive post before response ---



Thanks for you reply,

i have inserted this code in the sheet1 view code but when checked its
not working please advice where i have apply this script.

Regards,

Raja

Pertaining to the formula you can insert it in any cell you need the
result to be


--
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=115078

Hi Raja,

Assuming you have the data in Cell A1, use following formula:-
=99&REPT(0,12-LEN(A1)-2)&A1

--
Click on Yes, if it is useful.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)


New Delhi, India


"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


--
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=115078