The objective is to separate this kind of string from particular value "INPR0002268" means once you find 1st zero the string should print "0002268"

See more:

macros

VBA

So i am trying to read a string from a particular cell and then split the string from Zero on-wards and print the value in another cell

Issue-> In the local window when i am running the code it is showing the correct value "0002268 " but when i insert the same value in another cell its showing "2268".Please let me know what can be possible reason.

Sub newid()

Dim FirstZero As Long
Dim Name As Variant

Sheet2.Activate

Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E1").Value = "Orgnization ID"

Numberofrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To Numberofrow

Y = Range("A1").Offset(i, 0).Value
Lengthofstring = Len(Y)

FirstZero = InStr(1, Y, 0)
Debug.Print FirstZero

Name = Right(Y, Lengthofstring - (FirstZero - 1))

Range("A1").Offset(i, 0).Offset(0, 4).Value = Name

Next i

End Sub

What I have tried:

Sub newid()

Dim FirstZero As Long
Dim Name As Variant

Sheet2.Activate

Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E1").Value = "Orgnization ID"

Numberofrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To Numberofrow

Y = Range("A1").Offset(i, 0).Value
Lengthofstring = Len(Y)

FirstZero = InStr(1, Y, 0)
Debug.Print FirstZero

Name = Right(Y, Lengthofstring - (FirstZero - 1))

Range("A1").Offset(i, 0).Offset(0, 4).Value = Name

Next i

End Sub