Using Trim in a Userform
in [Programming]
|
From: Curtd on 23 May 2010 13:04 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: Rick Rothstein on 23 May 2010 13:28 The worksheet's TRIM function does not work the same as the built-in VB Trim function (which only removes spaces from the ends of the text). You can tap into the worksheet's TRIM function, to do what you want though, like this... ws.Cells(iRow, 2) = WorksheetFunction.Trim(txtProblem.Value) -- Rick (MVP - Excel) "Curtd" <Curtd(a)discussions.microsoft.com> wrote in message news:8813FFE8-A502-41DE-B1D6-EBDC798EFF00(a)microsoft.com... > 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: Rich Locus on 23 May 2010 22:39 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: Rich Locus on 23 May 2010 22:41 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: Rich Locus on 23 May 2010 22:42
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) |