Vba code to remove leading zeros in excel column I

[Sanoj]

Hello Guys...

I am looking for a macro code which should remove all leading zeros from column I and convert the column format to numbers without any decimals.

Could someone please help?

Example:

SAP_CC
0376
0376
0330
0360
0503
0503
0376
0584
0584
0584


Thanks
Sanoj

 

[jayalaxmi]

Hello Guys...

I am looking for a macro code which should remove all leading zeros from column I and convert the column format to numbers without any decimals.

Could someone please help?

Example:

SAP_CC
0376
0376
0330
0360
0503
0503
0376
0584
0584
0584


Thanks
Sanoj

see if this works for u

 

[Deepak]

Hi,

leading zero means it's formatted as text. You can also simply do the same in xl itself.

Meanwhile try this.

Sub change_text()
    [A2:A11].Value = [A2:A11].Value
End Sub

or

Sub change_text2()
    [A9999] = 1
    [A9999].Copy
    Range("A2:A11").PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply
    [A9999] = ""
End Sub

 

[Sanoj]

see if this works for u

Hi Jayalaxmi... Thankyou but its only changing the value of I2 only.

 

[Sanoj]

Hi,

leading zero means it's formatted as text. You can also simply do the same in xl itself.

Meanwhile try this.

Sub change_text()
    [A2:A11].Value = [A2:A11].Value
End Sub

or

Sub change_text2()
    [A9999] = 1
    [A9999].Copy
    Range("A2:A11").PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply
    [A9999] = ""
End Sub

Thanks Deepak its working as expected

 

[Khalid NGO]

Hi Sanoj,

for non-vba, you can use the following:
Enter 1 in any separate cell
Copy
Select your range (suppose A2:A11)
Press Ctrl+Alt+V (Paste Special)
select Multiply
OK

Regards,

 

[Sanoj]

Hi Sanoj,

for non-vba, you can use the following:
Enter 1 in any separate cell
Copy
Select your range (suppose A2:A11)
Press Ctrl+Alt+V (Paste Special)
select Multiply
OK

Regards,

Thanks Khalid but I need macro coding to embed with other code which I have.. I appreciate you help thank you so much.

 

[Deepak]

Hi Sanoj,

for non-vba, you can use the following:
Enter 1 in any separate cell
Copy
Select your range (suppose A2:A11)
Press Ctrl+Alt+V (Paste Special)
select Multiply
OK

Regards,

@Khalid
My second code is doing the same thing too.

 

[Khalid NGO]

@Khalid
My second code is doing the same thing too.

oh i didn't seen it sir,
I am a vba blind