|
|
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
<TomP[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >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:
[Quoted Text] > 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 <TomP[ at ]discussions.microsoft.com>
wrote:
[Quoted Text] >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" <TomP[ at ]discussions.microsoft.com> wrote in message
news:51BBA822-14ED-4D97-8D13-9C16B24C323C[ at ]microsoft.com...
[Quoted Text] >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"
<rick.newsNO.SPAM[ at ]NO.SPAMverizon.net> wrote:
[Quoted Text] >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" <ronrosenfeld[ at ]nospam.org> wrote in message
news:qsqml4tk2dngskm31mvs6rr5hk1kk5fmib[ at ]4ax.com...
[Quoted Text] > On Wed, 31 Dec 2008 01:19:35 -0500, "Rick Rothstein"
> <rick.newsNO.SPAM[ at ]NO.SPAMverizon.net> wrote:
>
>>S_without_underscores = WorksheetFunction.Trim(Replace(S, "_", " "))
>
> Nice and simple
> --ron
|
|
On Wed, 31 Dec 2008 11:43:08 -0500, "Rick Rothstein"
<rick.newsNO.SPAM[ at ]NO.SPAMverizon.net> wrote:
[Quoted Text] >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
|
|
|