How to add 99 before 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 sa
A1 it should add 99 before 10 digits automatically to make it 1
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

Raj

 

[Lars-Åke Aspelin]

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

 

[Pecoflyer]

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 sa
A1 it should add 99 before 10 digits automatically to make it 1
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 neede

--
Pecoflye

Cheers -

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

 

[Raja]

A possible formula solution > Code:

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

 

[Pecoflyer]

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 !

 

[DILipandey]

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.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India