Parsing out leading Zeros

[Jonathan]

I want to strip the leading zeros off of a bunch of
records. I want to transform data that looks like....

0GB12
0000100A
1234
02BD

into data that looks like....

GB12
100A
1234
2BD

I'm trying-

NoLeadingZeros: Mid([APExpCode],InStr([APExpCode],???))

I don't know what to put in the ??? area.

Thanks,

Jonathan

 

[Roger Carlson]

I would use a function:

Function StripLeadingZeros(TheValue As String) As String
Dim done As Boolean
done = False
Do Until done = True
If Left(TheValue, 1) = "0" Then
TheValue = Mid(TheValue, 2)
Else
done = True
End If
StripLeadingZeros = TheValue
Loop
End Function

Call it like this:

NoLeadingZeros:: StripLeadingZeros([APExpCode])

 

[Guest]

Here's a Visual Basic routine I wrote to do this

(maybe you can use it in VBA ? dunno - never programmed in straight vba

you use it like this

NewCode = gReplaceString(APExpCode,"0", ""

Now Here tis..


' Name: gReplaceStrin

' Purpose: Replace portions of a string with the specified character

' Inputs: psSource - string to be modifie
' psReplaceThis - portion of the of source string to be replace
' psWithThis - new tex
' Returns: Non

' Author: Ken Ramirez, 07.09.200

Public Function gReplaceString(psSource As String, psReplaceThis As String, psWithThis As String) As Strin
Dim liWTLen As Intege
Dim liRTLen As Intege
Dim liFPos As Intege
Dim lsWorking As Strin

liWTLen = Len(psWithThis
liRTLen = Len(psReplaceThis

lsWorking = psSourc

liFPos =

D
liFPos = InStr(liFPos, lsWorking, psReplaceThis
If liFPos = 1 The
lsWorking = psWithThis + mid(lsWorking, liRTLen + 1
liFPos = liFPos + liWTLe
End I
If liFPos > 1 The
If liFPos + liRTLen = Len(lsWorking) The
lsWorking = mid(lsWorking, 1, liFPos - 1) + psWithThi
Exit D
Els
lsWorking = mid(lsWorking, 1, liFPos - 1) + psWithThis + mid(lsWorking, liFPos + liRTLen
liFPos = liFPos + liWTLe
End I
End I
Loop While liFPos >

gReplaceString = lsWorkin
End Functio

 

[Guest]

Thanks Ken!

-----Original Message-----
Here's a Visual Basic routine I wrote to do this.

(maybe you can use it in VBA ? dunno - never programmed in straight vba )

you use it like this:

NewCode = gReplaceString(APExpCode,"0", "")

Now Here tis...

'
' Name: gReplaceString
'
' Purpose: Replace portions of a string with the specified characters
'
' Inputs: psSource - string to be modified
' psReplaceThis - portion of the of source string to be replaced
' psWithThis - new text
' Returns: None
'
' Author: Ken Ramirez, 07.09.2003
'
Public Function gReplaceString(psSource As String,

psReplaceThis As String, psWithThis As String) As String

Dim liWTLen As Integer
Dim liRTLen As Integer
Dim liFPos As Integer
Dim lsWorking As String

liWTLen = Len(psWithThis)
liRTLen = Len(psReplaceThis)

lsWorking = psSource

liFPos = 1

Do
liFPos = InStr(liFPos, lsWorking, psReplaceThis)
If liFPos = 1 Then
lsWorking = psWithThis + mid(lsWorking, liRTLen + 1)
liFPos = liFPos + liWTLen
End If
If liFPos > 1 Then
If liFPos + liRTLen = Len(lsWorking) Then
lsWorking = mid(lsWorking, 1, liFPos - 1) + psWithThis
Exit Do
Else
lsWorking = mid(lsWorking, 1, liFPos - 1)

+ psWithThis + mid(lsWorking, liFPos + liRTLen)