Greetings!

I have a worksheet which contains about 15000 rows, one
column of which is a dashed number sequence. Example:

0000123-45-67

I would like to remove the leading zeros. Note that
there are not always four zeros in front of the first
number (sometimes there are more, sometimes less). Is
there anything that can be done using VBA to remove the
zeros?

Thanks,
MARTY

Hi Marty,

You need a loop

sval = "00001-2-30"

For i = 1 To Len(sval)
If Mid(sval, i, 1) < "0" Then
Exit For
End If
Next i

sval = Right(sval, Len(sval) - i + 1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"MARTY" wrote in message
...
Greetings!

I have a worksheet which contains about 15000 rows, one
column of which is a dashed number sequence. Example:

0000123-45-67

I would like to remove the leading zeros. Note that
there are not always four zeros in front of the first
number (sometimes there are more, sometimes less). Is
there anything that can be done using VBA to remove the
zeros?

Thanks,
MARTY

It worked! Thanks medialint.

-----Original Message-----
I'm sure this can be done more efficiently, but this
will do it (works on column A of activesheet, modify as
needed):

Sub GoStripZero()
Dim nRow As Long
nRow = 1
With ActiveSheet
Do While .Cells(nRow, 1) ""
.Cells(nRow, 1) = StripZero(.Cells(nRow, 1))
nRow = nRow + 1
Loop
End With
End Sub

Private Function StripZero(strIn As String) As String
Dim n As Integer
Dim strOut As String
Dim boolNonZero As Boolean
Dim cmid As String
For n = 1 To Len(strIn)
cmid = Mid(strIn, n, 1)
Select Case cmid
Case "0":
If boolNonZero Then
strOut = strOut + cmid
End If
Case Else:
strOut = strOut + cmid
boolNonZero = True
End Select
Next n
StripZero = strOut
End Function

"MARTY" wrote:

Greetings!

I have a worksheet which contains about 15000 rows,
one
column of which is a dashed number sequence. Example:

0000123-45-67

I would like to remove the leading zeros. Note that
there are not always four zeros in front of the first
number (sometimes there are more, sometimes less). Is
there anything that can be done using VBA to remove
the
zeros?

Thanks,
MARTY


.

Bob:

I couldn't get this one to work. However, another user
provided a solution in a parallel thread which seemed to
do the trick.

I'm sure yours is simpler. I tied the loop to a command
button but it didn't seem to want to function.

Thanks anyway!

MARTY


-----Original Message-----
Hi Marty,

You need a loop

sval = "00001-2-30"

For i = 1 To Len(sval)
If Mid(sval, i, 1) < "0" Then
Exit For
End If
Next i

sval = Right(sval, Len(sval) - i + 1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"MARTY" wrote in
message
...
Greetings!

I have a worksheet which contains about 15000 rows, one
column of which is a dashed number sequence. Example:

0000123-45-67

I would like to remove the leading zeros. Note that
there are not always four zeros in front of the first
number (sometimes there are more, sometimes less). Is
there anything that can be done using VBA to remove the
zeros?

Thanks,
MARTY



.

If you have a large number of cells to strip you may want the fastest
possible
function. I think this is quite efficient:

Function StripLeadingZeros(strNumber As String) As String

Dim i As Long
Dim byteArray() As Byte

byteArray = strNumber

For i = 0 To UBound(byteArray) Step 2
If Not byteArray(i) = 48 Then
StripLeadingZeros = Right(strNumber, Len(strNumber) - i / 2)
Exit For
End If
Next

End Function

If somebody can show me something that is faster I would be interested.


RBS


"MARTY" wrote in message
...
Greetings!

I have a worksheet which contains about 15000 rows, one
column of which is a dashed number sequence. Example:

0000123-45-67

I would like to remove the leading zeros. Note that
there are not always four zeros in front of the first
number (sometimes there are more, sometimes less). Is
there anything that can be done using VBA to remove the
zeros?

Thanks,
MARTY

Your code wont work on non unicode systems (due to step2)

A small change in your code gives 5-10% improvement:
Change: StripLeadingZeros = Right(strNumber, Len(strNumber) - i / 2)
TO : StripLeadingZeros = Mid(strNumber, i / 2)


With an average of less than 4 leading zero's following code is faster:

Function TrimLeadZero$(ByVal strNumber$)
While strNumber Like "0*"
strNumber = Mid(strNumber, 2)
Wend
TrimLeadZero = strNumber
End Function

(I admit it is marginal, and advantage lost with longer strings)

<bg


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"RB Smissaert" wrote:

If you have a large number of cells to strip you may want the fastest
possible
function. I think this is quite efficient:

Function StripLeadingZeros(strNumber As String) As String

Dim i As Long
Dim byteArray() As Byte

byteArray = strNumber

For i = 0 To UBound(byteArray) Step 2
If Not byteArray(i) = 48 Then
StripLeadingZeros = Right(strNumber, Len(strNumber) - i / 2)
Exit For
End If
Next

End Function

If somebody can show me something that is faster I would be interested.

OK, to make the function work for both Unicode and non-Unicode it could be
this:

Function StripLeadingZeros(strNumber As String) As String

Dim i As Long
Dim byteArray() As Byte

byteArray = strNumber

For i = 0 To UBound(byteArray)
If Not byteArray(i) = 48 Then
If Not byteArray(i) = 0 Then
StripLeadingZeros = Mid(strNumber, i / 2 + 1)
Exit For
End If
End If
Next

End Function

I like your second function though for it's simplicity. Thanks for that.


RBS



"keepITcool" wrote in message
...

Your code wont work on non unicode systems (due to step2)

A small change in your code gives 5-10% improvement:
Change: StripLeadingZeros = Right(strNumber, Len(strNumber) - i / 2)
TO : StripLeadingZeros = Mid(strNumber, i / 2)


With an average of less than 4 leading zero's following code is faster:

Function TrimLeadZero$(ByVal strNumber$)
While strNumber Like "0*"
strNumber = Mid(strNumber, 2)
Wend
TrimLeadZero = strNumber
End Function

(I admit it is marginal, and advantage lost with longer strings)

<bg


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"RB Smissaert" wrote:

If you have a large number of cells to strip you may want the fastest
possible
function. I think this is quite efficient:

Function StripLeadingZeros(strNumber As String) As String

Dim i As Long
Dim byteArray() As Byte

byteArray = strNumber

For i = 0 To UBound(byteArray) Step 2
If Not byteArray(i) = 48 Then
StripLeadingZeros = Right(strNumber, Len(strNumber) - i / 2)
Exit For
End If
Next

End Function

If somebody can show me something that is faster I would be interested.

In case anybody wanted to test this:

Option Explicit
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private lngStartTime As Long
Private StartTime As Long
Private EndTime As Long

Sub StartTimer()

lngStartTime = timeGetTime()

End Sub

Function EndTimer()

'Get elapsed time in milliseconds
EndTimer = timeGetTime() - lngStartTime

End Function

Function StripLeadingZeros(ByVal strNumber As String) As String

Dim i As Long
Dim byteArray() As Byte

byteArray = strNumber

For i = 0 To UBound(byteArray)
If Not byteArray(i) = 48 Then
If Not byteArray(i) = 0 Then
StripLeadingZeros = Mid(strNumber, i / 2 + 1)
Exit For
End If
End If
Next

End Function

Function StripLeadingZeros2(ByVal strNumber As String) As String

While strNumber Like "0*"
strNumber = Mid(strNumber, 2)
Wend

StripLeadingZeros2 = strNumber

End Function

Private Function StripLeadingZeros3(ByVal strIn As String) As String

Dim n As Integer
Dim strOut As String
Dim boolNonZero As Boolean
Dim cmid As String

For n = 1 To Len(strIn)
cmid = Mid(strIn, n, 1)
Select Case cmid
Case "0":
If boolNonZero Then
strOut = strOut + cmid
End If
Case Else:
strOut = strOut + cmid
boolNonZero = True
End Select
Next n

StripLeadingZeros3 = strOut

End Function


Sub FillTestRange()

Dim c As Range

For Each c In Range(Cells(1), Cells(100, 100))
c = "000000000000456-147-114"
Next

End Sub

Sub teststripping()

Dim c As Range
Dim str As String

StartTimer

For Each c In Range(Cells(1), Cells(100, 100))
str = StripLeadingZeros(c.Text) '155 msecs
'str = StripLeadingZeros2(c.Text) '155 msecs
'str = StripLeadingZeros3(c.Text) '250 msecs
Next

MsgBox EndTimer

End Sub

Sub teststripping2()

Dim arr()
Dim i As Long
Dim c As Long
Dim str As String

arr = Range(Cells(1), Cells(100, 100))

StartTimer

For i = 1 To 100
For c = 1 To 100
'str = StripLeadingZeros(arr(i, c)) '47 msecs
'str = StripLeadingZeros2(arr(i, c)) '47 msecs
str = StripLeadingZeros3(arr(i, c)) '140 msecs
Next
Next

MsgBox EndTimer

End Sub

I will stick with StripLeadingZeros2.


RBS



"keepITcool" wrote in message
...

Your code wont work on non unicode systems (due to step2)

A small change in your code gives 5-10% improvement:
Change: StripLeadingZeros = Right(strNumber, Len(strNumber) - i / 2)
TO : StripLeadingZeros = Mid(strNumber, i / 2)


With an average of less than 4 leading zero's following code is faster:

Function TrimLeadZero$(ByVal strNumber$)
While strNumber Like "0*"
strNumber = Mid(strNumber, 2)
Wend
TrimLeadZero = strNumber
End Function

(I admit it is marginal, and advantage lost with longer strings)

<bg


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"RB Smissaert" wrote:

If you have a large number of cells to strip you may want the fastest
possible
function. I think this is quite efficient:

Function StripLeadingZeros(strNumber As String) As String

Dim i As Long
Dim byteArray() As Byte

byteArray = strNumber

For i = 0 To UBound(byteArray) Step 2
If Not byteArray(i) = 48 Then
StripLeadingZeros = Right(strNumber, Len(strNumber) - i / 2)
Exit For
End If
Next

End Function

If somebody can show me something that is faster I would be interested.

HUP HOLLAND!!!!!!!
--
keepITcool


"RB Smissaert" wrote:
I will stick with StripLeadingZeros2.


RBS

"RB Smissaert" wrote in message
...

If somebody can show me something that is faster I would be interested.


Since you asked ;)

Sub How_Would_Dana_Do_It()
'Assumes data is in column A, headings in row 1
Columns("A").Insert
With Range(Range("A2"), Range("B65536").End(xlUp).Offset(, -1))
.Formula = "=TEXT(LEFT(B2,FIND(""-"",B2)-1),""General"")" & _
"&MID(B2,FIND(""-"",B2),50)"
.Value = .Value
End With
Range("A1").Value = Range("B1").Value
Columns("B").Delete
End Sub

--
Hope this helps,
James dot Becker at NCR dot com
~
~
~
:wq!

Yes, let's hope they win.
What makes you think I am Dutch then?

RBS


"keepITcool" wrote in message
...
HUP HOLLAND!!!!!!!
--
keepITcool


"RB Smissaert" wrote:
I will stick with StripLeadingZeros2.


RBS

Haven't tested, but can't believe that is faster.
I would think the fastest way is to assign the sheet range to an array, run
one of the
mentioned fast functions on this array and then put the array in the sheet.
Even if it wasn't faster I prefer VBA above sheet formula's. It just looks
so messy.
I suppose it is just personal taste.

RBS


"Jim Becker" wrote in message
...
"RB Smissaert" wrote in message
...

If somebody can show me something that is faster I would be interested.


Since you asked ;)

Sub How_Would_Dana_Do_It()
'Assumes data is in column A, headings in row 1
Columns("A").Insert
With Range(Range("A2"), Range("B65536").End(xlUp).Offset(, -1))
.Formula = "=TEXT(LEFT(B2,FIND(""-"",B2)-1),""General"")" & _
"&MID(B2,FIND(""-"",B2),50)"
.Value = .Value
End With
Range("A1").Value = Range("B1").Value
Columns("B").Delete
End Sub

--
Hope this helps,
James dot Becker at NCR dot com
~
~
~
:wq!

Bart,
I didn't necessarily mean you were/are Dutch.
Your name sounds more Belgium or South African than Dutch anyway....

But since I beat your little gauntlet..
and
I AM Dutch..
and
since Holland is getting
more & more
ORANGE
every day...

I just couldnt resist
screaming


!!!HUP HOLLAND!!!!


(...just 2 more games to win....)

<bg
keepITnervous ;-)


"RB Smissaert" wrote:

Yes, let's hope they win.
What makes you think I am Dutch then?

RBS


"keepITcool" wrote in message
...
HUP HOLLAND!!!!!!!
--
keepITcool


"RB Smissaert" wrote:
I will stick with StripLeadingZeros2.


RBS

Well, I am Dutch, but I live in England.
I definitely will be watching tomorrow.
The Portugese beating the English saved me
from getting in a fight with my son!

RBS

"keepITcool" wrote in message
...
Bart,
I didn't necessarily mean you were/are Dutch.
Your name sounds more Belgium or South African than Dutch anyway....

But since I beat your little gauntlet..
and
I AM Dutch..
and
since Holland is getting
more & more
ORANGE
every day...

I just couldnt resist
screaming


!!!HUP HOLLAND!!!!


(...just 2 more games to win....)

<bg
keepITnervous ;-)


"RB Smissaert" wrote:

Yes, let's hope they win.
What makes you think I am Dutch then?

RBS


"keepITcool" wrote in message
...
HUP HOLLAND!!!!!!!
--
keepITcool


"RB Smissaert" wrote:
I will stick with StripLeadingZeros2.


RBS

Upon testing I concede the point. Your test program, as written, did not
provide a valid comparison, since it did not transfer the data back into the
range. I modified as per below and then ran ten tests of each on my
(admittedly) slow processor. Your StripLeadingZeros2 averaged 370 msecs vs.
my average of 607.

Sub teststripping2()

Dim arr()
Dim i As Long

arr = Range(Cells(1), Cells(15000, 1))

StartTimer

For i = 1 To 15000
arr(i, 1) = StripLeadingZeros2(arr(i, 1))
Next

Range(Cells(1), Cells(15000, 1)) = arr

MsgBox EndTimer

End Sub

"RB Smissaert" wrote in message
...
Haven't tested, but can't believe that is faster.
I would think the fastest way is to assign the sheet range to an array,
run
one of the
mentioned fast functions on this array and then put the array in the
sheet.
Even if it wasn't faster I prefer VBA above sheet formula's. It just looks
so messy.
I suppose it is just personal taste.

RBS


"Jim Becker" wrote in message
...
"RB Smissaert" wrote in message
...

If somebody can show me something that is faster I would be
interested.


Since you asked ;)

Sub How_Would_Dana_Do_It()
'Assumes data is in column A, headings in row 1
Columns("A").Insert
With Range(Range("A2"), Range("B65536").End(xlUp).Offset(, -1))
.Formula = "=TEXT(LEFT(B2,FIND(""-"",B2)-1),""General"")" & _
"&MID(B2,FIND(""-"",B2),50)"
.Value = .Value
End With
Range("A1").Value = Range("B1").Value
Columns("B").Delete
End Sub

--
Hope this helps,
James dot Becker at NCR dot com
~
~
~
:wq!

All replies so far have moved to Mid(...i...) instead of
Right(...Len(strNumber)...) so this could now be moot in the final
objective ... but for Smissaert - in the spirit of "fastest possible
function" - I would think about hoisting the Len(). My understanding
of VBA's interpretative nature suggests that it would recalculate it on
every iteration. Ouch!

And at the risk of sounding completely insane, is Byte actually faster
than Integer? Or even Long?!

Sincerely,
An obsolete .ASM dinosaur

Your code wont work on non unicode systems (due to step2)

A small change in your code gives 5-10% improvement:
Change: StripLeadingZeros = Right(strNumber, Len(strNumber) - i / 2)
TO : StripLeadingZeros = Mid(strNumber, i / 2)


With an average of less than 4 leading zero's following code is faster:

Function TrimLeadZero$(ByVal strNumber$)
While strNumber Like "0*"
strNumber = Mid(strNumber, 2)
Wend
TrimLeadZero = strNumber
End Function

(I admit it is marginal, and advantage lost with longer strings)

<bg


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"RB Smissaert" wrote:

If you have a large number of cells to strip you may want the fastest
possible
function. I think this is quite efficient:

Function StripLeadingZeros(strNumber As String) As String

Dim i As Long
Dim byteArray() As Byte

byteArray = strNumber

For i = 0 To UBound(byteArray) Step 2
If Not byteArray(i) = 48 Then
StripLeadingZeros = Right(strNumber, Len(strNumber) - i / 2)
Exit For
End If
Next

End Function

If somebody can show me something that is faster I would be interested.

Indeed, will change that and see what the
difference is.

RBS


<GatesAntiChrist wrote in message
. ..
All replies so far have moved to Mid(...i...) instead of
Right(...Len(strNumber)...) so this could now be moot in the final
objective ... but for Smissaert - in the spirit of "fastest possible
function" - I would think about hoisting the Len(). My understanding
of VBA's interpretative nature suggests that it would recalculate it on
every iteration. Ouch!

And at the risk of sounding completely insane, is Byte actually faster
than Integer? Or even Long?!

Sincerely,
An obsolete .ASM dinosaur

Your code wont work on non unicode systems (due to step2)

A small change in your code gives 5-10% improvement:
Change: StripLeadingZeros = Right(strNumber, Len(strNumber) - i / 2)
TO : StripLeadingZeros = Mid(strNumber, i / 2)


With an average of less than 4 leading zero's following code is faster:

Function TrimLeadZero$(ByVal strNumber$)
While strNumber Like "0*"
strNumber = Mid(strNumber, 2)
Wend
TrimLeadZero = strNumber
End Function

(I admit it is marginal, and advantage lost with longer strings)

<bg


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"RB Smissaert" wrote:

If you have a large number of cells to strip you may want the fastest
possible
function. I think this is quite efficient:

Function StripLeadingZeros(strNumber As String) As String

Dim i As Long
Dim byteArray() As Byte

byteArray = strNumber

For i = 0 To UBound(byteArray) Step 2
If Not byteArray(i) = 48 Then
StripLeadingZeros = Right(strNumber, Len(strNumber) - i /
2)
Exit For
End If
Next

End Function

If somebody can show me something that is faster I would be interested.