Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 13th 18, 12:08 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2018
Posts: 3
Default 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


  #2   Report Post  
Old October 13th 18, 12:19 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,647
Default Remove leading zeros from string

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
  #3   Report Post  
Old October 14th 18, 05:00 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2018
Posts: 2
Default Remove leading zeros from string

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", "")
  #4   Report Post  
Old October 14th 18, 06:55 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 981
Default Remove leading zeros from string

El sbado, 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
  #5   Report Post  
Old October 14th 18, 10:33 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2018
Posts: 3
Default Remove leading zeros from string


?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


  #6   Report Post  
Old October 14th 18, 02:52 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 981
Default Remove leading zeros from string


?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
  #7   Report Post  
Old October 14th 18, 03:09 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 981
Default Remove leading zeros from string

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Remove leading zeros Machel Excel Discussion (Misc queries) 9 February 14th 08 01:07 AM
How can I remove leading zeros? AccessHelp Excel Programming 17 September 28th 07 07:12 PM
How can I remove leading zeros? Gary Keramidas Excel Programming 0 September 26th 07 10:03 PM
REMOVE LEADING ZEROS ichihina Excel Worksheet Functions 2 March 14th 07 08:58 PM
Using VBA to remove leading zeros Michael G. Thomas Excel Programming 5 September 12th 04 05:33 PM


All times are GMT +1. The time now is 04:28 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017