Using Replace function to remove white space, why isn't this working?

Question

Hello,

I created this function to remove white space(s) from strings(text).

Example: I want to make sure that there is only one space between words regardless of how many spaces a person enters.

So if there are 10 spaces between words; it will cut it to one. I know this sounds unusual but I am just curious and for the sake of knowledge.

Like this:

Public Function TrmSpace(RemoveSpace As String) As String
RemoveSpace = Replace(RemoveSpace, "    ", " ")
RemoveSpace = Replace(RemoveSpace, "   ", " ")
RemoveSpace = Replace(RemoveSpace, "  ", " ")
TrmSpace = RemoveSpace
End Function

The spaces seem to be inconsistent, is there any other way I can do this?

 

Thanks everyone

Answer 1

hi,

 

try with the Trim excel function

 

RemoveSpace = Trim(TheText)

 --

isabelle

 

Le 2011-12-20 23:01, Synthologic a écrit :

> Hello,

>

> I created this function to remove white space(s) from strings(text).

>

> Example: I want to make sure that there is only one space between words regardless of how many spaces a person enters.

>

> So if there are 10 spaces between words; it will cut it to one. I know this sounds unusual but I am just curious and for the sake of knowledge.

>

> Like this:

>

> Public  Function  TrmSpace(RemoveSpaceAs  String)As  String

> RemoveSpace =Replace(RemoveSpace,"     ","  ")

> RemoveSpace =Replace(RemoveSpace,"    ","  ")

> RemoveSpace =Replace(RemoveSpace,"   ","  ")

> TrmSpace = RemoveSpace

> End  Function

>

> The spaces seem to be inconsistent, is there any other way I can do this?

>

> Thanks everyone

>

 

Answer 2

hi,

 

try with the Trim excel function

 

RemoveSpace = Trim(TheText)

 --

isabelle

 

Le 2011-12-20 23:01, Synthologic a écrit :

> Hello,

>

> I created this function to remove white space(s) from strings(text).

>

> Example: I want to make sure that there is only one space between words regardless of how many spaces a person enters.

>

> So if there are 10 spaces between words; it will cut it to one. I know this sounds unusual but I am just curious and for the sake of knowledge.

>

> Like this:

>

> Public  Function  TrmSpace(RemoveSpaceAs  String)As  String

> RemoveSpace =Replace(RemoveSpace,"     ","  ")

> RemoveSpace =Replace(RemoveSpace,"    ","  ")

> RemoveSpace =Replace(RemoveSpace,"   ","  ")

> TrmSpace = RemoveSpace

> End  Function

>

> The spaces seem to be inconsistent, is there any other way I can do this?

>

> Thanks everyone

>

 

I don't want to trim all the space, I want one space to remain on the left and the right.

Answer 3

Public Function TrmSpace(RemoveSpace As String) As String
Dim i As Long
For i = 1 To 5
  RemoveSpace = Replace(RemoveSpace, "   ", " ")
  RemoveSpace = Replace(RemoveSpace, "  ", " ")
Next i
TrmSpace = RemoveSpace
End Function

---

Greg Maxey Please visit my website at: http://gregmaxey.mvps.org/word_tips.htm

Answer 4

On Wed, 21 Dec 2011 05:14:45 +0000, Synthologic wrote:

 

>I don't want to trim all the space, I want one space to remain on the left and the right.

 

 

To ignore the leftmost and rightmost characters:

 

Function TrimSpace(s As String) As String

    TrimSpace = Left(s, 1) & _

        WorksheetFunction.Trim(Mid(s, 2, Len(s) - 2)) _

        & Right(s, 1)

End Function

 

To ensure a space at the left and right:

 

Function TrimSpace(s As String) As String

    s = " " & WorksheetFunction.Trim(s) & " "

End Function

 

Obviously, both of those can also be represented as worksheet formulas.

 

---

Ron

Answer 5

Another function will be, like this below, it will work only for Text fields:

 

Public Function TrimSpaces(strString As String) As String
    'Returns a string minus additional spaces
    Dim strLetter As String, strNew As String
    Dim i As Integer, intScount As Integer
    For i = 1 To Len(strString)
        'Loop through the text and get rid of any surplus spaces
        strLetter = Right(Left(strString, i), 1)
        If strLetter = " " Then
            intScount = intScount + 1
        Else
            intScount = 0
        End If
        'Check if less than two spaces, if so, add letter
        If intScount < 2 Then strNew = strNew & strLetter
    Next
    TrimSpaces = Trim(strNew)
End Function

Hope this helps,

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Answer 6

And here's another method using regular expressions:

=============================
Function TrimInternalSpaces(s As String)
  Dim re As Object
Set re = CreateObject("vbscript.regexp")
With re
    .Pattern = "( ){2,}"  'single space within the ( )
    .Global = True
End With
TrimInternalSpaces = re.Replace(s, " ") 'single space within the " "

End Function
==========================

---

Ron

Answer 7

Couldn't resist(vba):

Function news(s2)
Do
s2 = Replace(s2, "  ", " ")
n = InStr(1, s2, "  ")
Loop Until n = 0
news = s2
End Function

 

 

 

Answer 8

TRIM function, Removes all spaces from text except for single spaces between words.

 

--

isabelle

 

Le 2011-12-21 00:14, Synthologic a écrit :

> I don't want to trim all the space, I want one space to remain on the left and the right.

 

Answer 9

WOW! What a rush of great resources and knowledge.

 

You guys all rock!

 

Thanks everyone!