Convert 2 blanks to 1

[projecttoday]

Is there any way of converting multiple blanks in a string between words to just one blank?

 

[GingerR]

An example would help.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[projecttoday]

John<blank>Q.<blank><blank>Smith
becomes
John<blank>Q.<blank>Smith

Little<blank>Rock,<blank><blank>Arkansas
becomes
Little<blank>Rock,<blank>Arkansas

 

[TheAceMan1]

How are ya projecttoday . . .

Try the following function:

[blue]Public Function TrimSpaces(ByVal Dat As String) As String
   
   Do Until InStr(1, Dat, "  ") = 0
      Dat = Replace(Dat, "  ", " ")
   Loop
   
   TrimSpaces = Dat
      
End Function[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[HarleyQuinn]

Or another way:

Public Function ReplaceSpaces(strInput As String) As String
Dim re As Object

Set re = CreateObject("VBScript.RegExp")

With re
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "\s{2,}"
    ReplaceSpaces = .Replace(strInput, " ")
End With

Set re = Nothing
End Function

Hope this helps

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[projecttoday]

Thank you.