ExcelBanter - Using Trim in a Userform

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Using Trim in a Userform (https://www.excelbanter.com/excel-programming/442764-using-trim-userform.html)

Curtd

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)

Rick Rothstein

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)

Rich Locus

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)

Rich Locus

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)

Rich Locus

Using Trim in a Userform

Rich Locus

Using Trim in a Userform

Rick Rothstein

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)

Rich Locus

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)

.

Rich Locus

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)

.

Rick Rothstein

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)

.

Rick Rothstein

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)

.

Rich Locus

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)

.

.

Chip Pearson

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.

All times are GMT +1. The time now is 06:10 PM.