| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Trim in a Userform
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) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Trim in a Userform
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" wrote in message ... 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) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Trim in a Userform
Hello:
Here's a little space stripper function you can call. 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) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Trim in a Userform
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) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Trim in a Userform
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) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Trim in a Userform
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) |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Trim in a Userform
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" wrote in message ... 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) |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Trim in a Userform
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" wrote in message ... 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) . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Trim in a Userform
Rick:
Here is the corrected code which can be used to strip any character... not just spaces, by changing the literal. However, for spaces, you definitely have the correct solutions. Regards to the MVP. 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 End If Else j = j + 1& Mid(InputString, j, 1&) = Mid(InputString, i, 1&) boolLastItemWasSpace = False End If Next i StripExtraSpaces = Left(InputString, j) ' String Is Now Shorter End Function -- Rich Locus Logicwurks, LLC "Rich Locus" wrote: 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" wrote in message ... 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) . |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Trim in a Userform
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" wrote in message ... 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" wrote in message ... 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) . |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Trim in a Userform
I'm not sure how much sense it makes to remove non-space characters from the
beginning and end of a text string and then collapse multiple internal non-space characters down to a single occurrence of that non-space character, but if that is what you want, then I would probably do it this way (where you can pass in the non-space character in the optional 2nd argument, which defaults to the space character if omitted)... Function StripExtraSpaces(ByVal InputString As String, _ Optional ReplaceChar As String = " ") As String If ReplaceChar < " " Then InputString = Replace(InputString, " ", Chr(1)) InputString = Replace(InputString, ReplaceChar, " ") End If InputString = Trim(InputString) Do While InStr(InputString, " ") InputString = Replace(InputString, " ", " ") Loop If ReplaceChar < " " Then InputString = Replace(InputString, " ", ReplaceChar) InputString = Replace(InputString, Chr(1), " ") End If StripExtraSpaces = InputString End Function -- Rick (MVP - Excel) "Rich Locus" wrote in message ... Rick: Here is the corrected code which can be used to strip any character... not just spaces, by changing the literal. However, for spaces, you definitely have the correct solutions. Regards to the MVP. 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 End If Else j = j + 1& Mid(InputString, j, 1&) = Mid(InputString, i, 1&) boolLastItemWasSpace = False End If Next i StripExtraSpaces = Left(InputString, j) ' String Is Now Shorter End Function -- Rich Locus Logicwurks, LLC "Rich Locus" wrote: 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" wrote in message ... 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) . |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Trim in a Userform
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" wrote in message ... 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" wrote in message ... 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) . . |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Trim in a Userform
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" 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| TRIM Help ! | Excel Discussion (Misc queries) | |||
| TRIM | Excel Discussion (Misc queries) | |||
| VLOOKUP Trim vs. No Trim | Excel Programming | |||
| VBA Trim and Application.worksheetfunction.Trim | Excel Programming | |||
| Trim like worksheet Trim | Excel Programming | |||
Linear Mode
