I have an object that is filled with many underscores between texts. I
recently got help from your forum on how to remove leading underscores which
works great and now I need help in replacing a line of underscores between
words with a single space.

Thank you again for your help!

What sort of "object" are you referring to? If the text of the
"object" is in a variable named S, you can use code like the
following. The first Replace removes all the underscore characters.
The loop single spaces the remaining text (converting 2 spaces to 1
space):

Dim S As String
Dim N As Long
S = "your string here"
S = Replace(S, "-", vbNullString)
N = InStr(1, S, Space(2))
Do Until N = 0
S = Replace(S, Space(2), Space(1))
N = InStr(1, S, Space(2))
Loop
Debug.Print S

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Tue, 30 Dec 2008 13:37:34 -0800, TomP
wrote:

I have an object that is filled with many underscores between texts. I
recently got help from your forum on how to remove leading underscores which
works great and now I need help in replacing a line of underscores between
words with a single space.

Thank you again for your help!

I'm thinking this might be more what you are looking for. When I ran Chip's
code, I got a string without spaces at all.

Sub merge()
Dim S As String
S = "Your_String"
S = Replace(S, "_", Space(1))
MsgBox S
End Sub

"TomP" wrote:

I have an object that is filled with many underscores between texts. I
recently got help from your forum on how to remove leading underscores which
works great and now I need help in replacing a line of underscores between
words with a single space.

Thank you again for your help!

On Tue, 30 Dec 2008 13:37:34 -0800, TomP
wrote:

I have an object that is filled with many underscores between texts. I
recently got help from your forum on how to remove leading underscores which
works great and now I need help in replacing a line of underscores between
words with a single space.

Thank you again for your help!

============================
Option Explicit
Function ReplaceUnderscore(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\s*_+\s*"
ReplaceUnderscore = re.Replace(str, " ")
End Function
==========================

This routine will also replace any spaces that might be before or after the
underscore. so:

Now is________the time -- Now is the time

Now is ____________ the time -- Now is the time

If that is not desired behavior, then change
re.Pattern = "_+"

--ron

If your text is in a variable named S, then this single line of code will
remove leading and trailing underscores and reduce all internal underscores
to a single space...

S_without_underscores = WorksheetFunction.Trim(Replace(S, "_", " "))

The above statement assumes either that there are no internal spaces or that
if there are, they will be combined with the spaces produced by converting
the underscores to spaces and then reduced to a single space afterwards. I
am pretty sure that it is what you are after. However, if you must preserve
existing multiple internal spaces, then this single line of code may do what
you want...

S_without_underscores = Replace(WorksheetFunction.Trim(Replace(Replace( _
S, " ", Chr(1)), "_", " ")), Chr(1), " ")

--
Rick (MVP - Excel)


"TomP" wrote in message
...
I have an object that is filled with many underscores between texts. I
recently got help from your forum on how to remove leading underscores
which
works great and now I need help in replacing a line of underscores between
words with a single space.

Thank you again for your help!

On Wed, 31 Dec 2008 01:19:35 -0500, "Rick Rothstein"
wrote:

S_without_underscores = WorksheetFunction.Trim(Replace(S, "_", " "))

Nice and simple
--ron

Thanks... the worksheet TRIM function has a definite advantage over VB's
Trim function for this particular question.

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Wed, 31 Dec 2008 01:19:35 -0500, "Rick Rothstein"
wrote:

S_without_underscores = WorksheetFunction.Trim(Replace(S, "_", " "))

Nice and simple
--ron

On Wed, 31 Dec 2008 11:43:08 -0500, "Rick Rothstein"
wrote:

Thanks... the worksheet TRIM function has a definite advantage over VB's
Trim function for this particular question.

Assuming, as you pointed out, that the OP wants all <multiple spaces in the
document to be condensed to a single <space.
--ron

Thank you for your help! The Function worked perfectly! Happy New Year.

Tom

"Ron Rosenfeld" wrote:

On Tue, 30 Dec 2008 13:37:34 -0800, TomP
wrote:

I have an object that is filled with many underscores between texts. I
recently got help from your forum on how to remove leading underscores which
works great and now I need help in replacing a line of underscores between
words with a single space.

Thank you again for your help!

============================
Option Explicit
Function ReplaceUnderscore(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\s*_+\s*"
ReplaceUnderscore = re.Replace(str, " ")
End Function
==========================

This routine will also replace any spaces that might be before or after the
underscore. so:

Now is________the time -- Now is the time

Now is ____________ the time -- Now is the time

If that is not desired behavior, then change
re.Pattern = "_+"

--ron