Using Trim in a Userform
in [Programming]
|
From: Rich Locus on 24 May 2010 02:13 Wow!! You are good! It's always smart to learn from the masters :) -- Rich Locus Logicwurks, LLC "Rick Rothstein" wrote: > Well, if you want to do this without calling the WorksheetFunction property > (of the Application object), then I would use this (still) much shorter > macro... > > Function StripExtraSpaces(ByVal InputString As String) As String > StripExtraSpaces = Trim(InputString) > Do While InStr(StripExtraSpaces, " ") > StripExtraSpaces = Replace(StripExtraSpaces, " ", " ") > Loop > End Function > > -- > Rick (MVP - Excel) > > > > "Rich Locus" <RichLocus(a)discussions.microsoft.com> wrote in message > news:30E75A12-6D9C-4ECD-B508-54F7BA94E7C6(a)microsoft.com... > > Rick: > > You are correct. I like your approach better. I am, however, still going > > to fix the two space bug in the function. > > Regards, > > -- > > Rich Locus > > Logicwurks, LLC > > > > > > "Rick Rothstein" wrote: > > > >> When I run your code, I get two spaces left between words, not one. > >> > >> I'm guessing you didn't see my first posting in this thread, did you? > >> Here > >> is what I posted modified to fit your function header... > >> > >> Function StripExtraSpaces(ByVal InputString As String) As String > >> StripExtraSpaces = WorksheetFunction.Trim(InputString) > >> End Function > >> > >> This code, which is much shorter than yours, outputs what I think you > >> intended your function to output. > >> > >> -- > >> Rick (MVP - Excel) > >> > >> > >> > >> "Rich Locus" <RichLocus(a)discussions.microsoft.com> wrote in message > >> news:EA5C74DA-22EE-4B4C-BE44-8DEDF3230804(a)microsoft.com... > >> > Hello: > >> > If this function solves your problem, > >> > please check the "Answers My Question" button. > >> > > >> > This function trims leading and trailing spaces, > >> > and then allows only one single space between > >> > contiguous words in a string: > >> > > >> > Function StripExtraSpaces(ByVal InputString As String) As String > >> > Dim i, j As Long > >> > Dim boolLastItemWasSpace As Boolean > >> > j = 0 > >> > boolLastItemWasSpace = False > >> > > >> > InputString = Trim(InputString) > >> > For i = 1 To Len(InputString) > >> > If Mid(InputString, i, 1&) = " " Then > >> > If Not boolLastItemWasSpace Then > >> > j = j + 1& > >> > Mid(InputString, j, 1&) = Mid(InputString, i, 1&) > >> > boolLastItemWasSpace = True > >> > Else > >> > boolLastItemWasSpace = False > >> > End If > >> > Else > >> > j = j + 1& > >> > Mid(InputString, j, 1&) = Mid(InputString, i, 1&) > >> > End If > >> > Next i > >> > StripExtraSpaces = Left(InputString, j) ' String Is Now Shorter > >> > End Function > >> > > >> > -- > >> > Rich Locus > >> > Logicwurks, LLC > >> > > >> > > >> > "Curtd" wrote: > >> > > >> >> I have a Userform that I fill out and have the information entered in > >> >> a > >> >> cell > >> >> on a worksheet, I am trying to use Trim when entering it in the cell > >> >> to > >> >> remove any duplicate spaces. Can someone help me out and let me know > >> >> why > >> >> this isn't working and what I need to change to get it to work. Thank > >> >> you > >> >> for your help. > >> >> > >> >> ws.Cells(iRow, 2) = Trim(txtProblem.Value) > >> > >> . > >> > . >
From: Chip Pearson on 24 May 2010 06:02
Here's a function from my standard library that I use to trim and single space a string. It is pure VBA, with no reliance on Excel, so can be used in any VBA application. Function SingleSpace(S As String) As String Dim T As String Dim N As Long T = Trim(S) N = InStr(1, T, Space(2), vbBinaryCompare) Do Until N = 0 T = Replace(T, Space(2), Space(1)) N = InStr(1, T, Space(2), vbBinaryCompare) Loop SingleSpace = T End Function Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Mon, 24 May 2010 00:14:12 -0400, "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote: >When I run your code, I get two spaces left between words, not one. > >I'm guessing you didn't see my first posting in this thread, did you? Here >is what I posted modified to fit your function header... > >Function StripExtraSpaces(ByVal InputString As String) As String > StripExtraSpaces = WorksheetFunction.Trim(InputString) >End Function > >This code, which is much shorter than yours, outputs what I think you >intended your function to output. |