Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #110750 > unrolled thread

Remove leading zeros from string

Back to article view | Back to microsoft.public.excel.programming

Contents

  Remove leading zeros from string Tatsujin <tatsujin.3.r@gmail.com> - 2018-10-13 04:08 -0700
    Re: Remove leading zeros from string Claus Busch <claus_busch@t-online.de> - 2018-10-13 13:19 +0200
    Re: Remove leading zeros from string Ramon-México <cesaramon@gmail.com> - 2018-10-13 21:00 -0700
      Re: Remove leading zeros from string GS <gs@v.invalid> - 2018-10-14 01:55 -0400
        Re: Remove leading zeros from string Tatsujin <tatsujin.3.r@gmail.com> - 2018-10-14 02:33 -0700
          Re: Remove leading zeros from string GS <gs@v.invalid> - 2018-10-14 09:52 -0400
            Re: Remove leading zeros from string GS <gs@v.invalid> - 2018-10-14 10:09 -0400

#110750 — Remove leading zeros from string

Suppose I have a string like this:

"32 01 22 88 03"

I need to remove all the leading zeros in the string.  The result should be:

"32 1 22 88 3"

What's the easiest way to go about this?  Thanks!

Mr. T

[toc] | [next] | [standalone]

#110751

Hi,

Am Sat, 13 Oct 2018 04:08:32 -0700 (PDT) schrieb Tatsujin:

> Suppose I have a string like this:
> 
> "32 01 22 88 03"
> 
> I need to remove all the leading zeros in the string.  The result should be:
> 
> "32 1 22 88 3"

Find & Select => Replace and replace space and 0 (" 0") with space (" ")


Regards
Claus B.
-- 
Windows10
Office 2016

[toc] | [prev] | [next] | [standalone]

#110752

El sábado, 13 de octubre de 2018, 6:08:39 (UTC-5), Tatsujin  escribió:
> Suppose I have a string like this:
> 
> "32 01 22 88 03"
> 
> I need to remove all the leading zeros in the string.  The result should be:
> 
> "32 1 22 88 3"
> 
> What's the easiest way to go about this?  Thanks!
> 
> Mr. T

?Replace("32 01 22 88 03", "0", "")

[toc] | [prev] | [next] | [standalone]

#110753

> El sábado, 13 de octubre de 2018, 6:08:39 (UTC-5), Tatsujin  escribió:
>> Suppose I have a string like this:
>> 
>> "32 01 22 88 03"
>> 
>> I need to remove all the leading zeros in the string.  The result should be:
>> 
>> "32 1 22 88 3"
>> 
>> What's the easiest way to go about this?  Thanks!
>> 
>> Mr. T
>
> ?Replace("32 01 22 88 03", "0", "")

Oops! That's going to replac *ALL* zeros; - the task is to replace *LEADING* 
ZEROS ONLY!!

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#110754

> >
> > ?Replace("32 01 22 88 03", "0", "")
> 
> Oops! That's going to replac *ALL* zeros; - the task is to replace *LEADING* 
> ZEROS ONLY!!
> 

I devised the following solution. Maybe using regular expressions
is overkill, but it worked.  Here it is:

Public Sub MyReplace()
' Include"Microsoft VBScript Regular Expressions 5.5" in Tools->References
Dim regEx As New VBScript_RegExp_55.RegExp

Dim s1 As String
Dim sFinal As String
Dim sExample As String

sExample = "01 07 08 22 88 06 04"

' Replace leading zeros (in middle of line)
regEx.Pattern = " 0"
regEx.Global = True
regEx.IgnoreCase = False
s1 = regEx.Replace(sExample, " ")

' Remove leading zeros (at beginning of line)
regEx.Pattern = "^0"
regEx.Global = True
regEx.IgnoreCase = False
sFinal = regEx.Replace(s1, "")

MsgBox sFinal

End Sub


- Robert Crandall

[toc] | [prev] | [next] | [standalone]

#110755

>>> 
>>> ?Replace("32 01 22 88 03", "0", "")
>> 
>> Oops! That's going to replac *ALL* zeros; - the task is to replace *LEADING* 
>> ZEROS ONLY!!
>> 
>
> I devised the following solution. Maybe using regular expressions
> is overkill, but it worked.  Here it is:
>
> Public Sub MyReplace()
> ' Include"Microsoft VBScript Regular Expressions 5.5" in Tools->References
> Dim regEx As New VBScript_RegExp_55.RegExp
>
> Dim s1 As String
> Dim sFinal As String
> Dim sExample As String
>
> sExample = "01 07 08 22 88 06 04"
>
> ' Replace leading zeros (in middle of line)
> regEx.Pattern = " 0"
> regEx.Global = True
> regEx.IgnoreCase = False
> s1 = regEx.Replace(sExample, " ")
>
> ' Remove leading zeros (at beginning of line)
> regEx.Pattern = "^0"
> regEx.Global = True
> regEx.IgnoreCase = False
> sFinal = regEx.Replace(s1, "")
>
> MsgBox sFinal
>
> End Sub
>
>
> - Robert Crandall

Yep, too much typing for me! I already have functions for various filtering 
needs; here's one for removing leading zeros...


Function NoPad_Zeros$(sText$)
' Returns a string with no leading zeros
  Dim vTmp, n&
  Application.Volatile

  vTmp = Split(sText, " ")
  For n = LBound(vTmp) To UBound(vTmp)
    vTmp(n) = CLng(vTmp(n))
  Next 'n
  NoPad_Zeros = Join(vTmp, " ")
End Function

..that you can call from code OR use as a cell formula.

In the IW:
  ?nopad_zeros("01 07 08 22 88 06 04")
  Returns 1 7 8 22 88 6 4

In a cell:
  A1 contains 01 07 08 22 88 06 04
  B1 contains =nopad_zeros(A1)
     displays 1 7 8 22 88 6 4

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#110756

For example...


Function FilterString$(ByVal TextIn$, Optional IncludeChars$, _
                       Optional IncludeLetters As Boolean = True, _
                       Optional IncludeNumbers As Boolean = True)
' Filters out all unwanted characters in a string.
' Arguments:  TextIn          The string being filtered.
'             IncludeChars    [Optional] Any non alpha-numeric characters to 
keep.
'             IncludeLetters  [Optional] Keeps any letters.
'             IncludeNumbers  [Optional] Keeps any numbers.
'
' Returns:    String containing only wanted characters.
' Comments:   Works very fast using the Mid$() function over other methods.

Const sSource As String = "FilterString()"

  'The basic characters to always keep by default
  Const sLetters  As String = "abcdefghijklmnopqrstuvwxyz"
  Const sNumbers  As String = "0123456789"

  Dim i&, sKeepers$

  sKeepers = IncludeChars
  If IncludeLetters Then _
      sKeepers = sKeepers & sLetters & UCase(sLetters)
  If IncludeNumbers Then sKeepers = sKeepers & sNumbers

  For i = 1 To Len(TextIn)
    If InStr(sKeepers, Mid$(TextIn, i, 1)) Then _
      FilterString = FilterString & Mid$(TextIn, i, 1)
  Next
End Function 'FilterString()

In the IW:
  ?nopad_zeros(filterstring("Part# 0000006004",,false))
  Returns 6004

  ?nopad_zeros(filterstring("Part# 0000060040",,false))
  Returns 60040

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web