im trying to remove digits using a personalized function-CodePudding

Im trying to erase the first 10 digits of all rows in a excel column using a vba personalized formula(i do this instead or a regular funciton because i still need to keep modyfing the data after this step) but my function is not working

My code:

PUBLIC FUNCTION removefirst (rng as String, cnt As long)
removefirst = Right(rng, Len(rng)- cnt)
End FUNCTION

I do the following on excel = removefirst(D2, 10) but is giving a pop up error message

CodePudding user response：

I believe you'll recieve errors when the input you pass into the function won't have the length you'd want to trim to begin with. Unless you'd want to alter the output a bit more I don't see the point of recreating LEFT() or RIGHT() or MID() functionality, but if it's a must then maybe:

Option Explicit

Public Function RemoveFirst(nr As Long, Optional ByVal cnt As Long = 1) As Long
    RemoveFirst = IIf(Len(nr) > cnt, Mid(nr, cnt   1), nr)
End Function

This will now test against the lenght of your input and will return this original input if the 'cnt' is more than the length to work with. Change the 3rd parameter as you like (maybe into '0'?).

Note: Removing the first n digits is not the same as removing the first n characters. So be sure that your input is indeed all numbers.