How to replace a string in a certain string?

C

cyberdude

Hi,

I have a string like this:

"A, B, C"

and I need to turn it to

"A, B and C"

, could someone tell me how to do the replacement? In some cases,
there are more than three items in the string (for example, "A, B, C,
D, E, F") Could someone tell me how to do the replacement in this
string with more items? Thank you.

Mike
 
M

macropod

Hi cyberdude,

Here's one way:
Sub Demo()
Dim i As Integer
Dim StrArry()
Dim strTxt As String
With Selection
For i = 0 To UBound(Split(Selection, ",")) - 1
ReDim Preserve StrArry(i)
StrArry(i) = Split(Selection, ",")(i)
strTxt = Join(StrArry(), ",")
Next
MsgBox strTxt & " and " & Split(Selection, ",")(UBound(Split(Selection, ",")))
End With
End Sub
 
H

Helmut Weber

Hi Mike,

with the text from your post as sample,
this one works for me:

Sub Test4()
Dim x As Long
Dim sTmp As String
Dim rTmp As Range
Set rTmp = ActiveDocument.Range
With rTmp.Find
.Text = "([A-Z]), ([A-Z])[!,]"
.MatchWildcards = True
While .Execute
rTmp.End = rTmp.End - 1
' rtmp.Select
x = rTmp.End
rTmp.End = rTmp.End + 5
' rtmp.Select
sTmp = rTmp.Text
If Right(sTmp, 5) <> " and " Then
' MsgBox "Parse"
rTmp.End = rTmp.End - 5
sTmp = rTmp.Text
rTmp.Text = Left(sTmp, 1) & " and " & Right(sTmp, 1)
End If
rTmp.start = x
rTmp.End = ActiveDocument.Range.End
Wend
End With
End Sub

The advantage may be, that this macro can be executed
many times without producing "A and B and C and D ..."

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP
 
M

macropod

Taking a leaf out of Helmut's book:
Sub Demo()
Dim i As Integer
Dim StrArry()
Dim strTxt As String
If InStr(Selection, "and") = 0 And InStr(Selection, ",") > 0 Then
For i = 0 To UBound(Split(Selection, ",")) - 1
ReDim Preserve StrArry(i)
StrArry(i) = Split(Selection, ",")(i)
strTxt = Join(StrArry(), ",")
Next
MsgBox strTxt & " and " & Split(Selection, ",")(UBound(Split(Selection, ",")))
End If
End Sub

--
Cheers
macropod
[MVP - Microsoft Word]

PS: to replace the string in the document, simply change 'MsgBox' to 'Selection.TypeText'


macropod said:
Hi cyberdude,

Here's one way:
Sub Demo()
Dim i As Integer
Dim StrArry()
Dim strTxt As String
With Selection
For i = 0 To UBound(Split(Selection, ",")) - 1
ReDim Preserve StrArry(i)
StrArry(i) = Split(Selection, ",")(i)
strTxt = Join(StrArry(), ",")
Next
MsgBox strTxt & " and " & Split(Selection, ",")(UBound(Split(Selection, ",")))
End With
End Sub

--
Cheers
macropod
[MVP - Microsoft Word]


cyberdude said:
Hi,

I have a string like this:

"A, B, C"

and I need to turn it to

"A, B and C"

, could someone tell me how to do the replacement? In some cases,
there are more than three items in the string (for example, "A, B, C,
D, E, F") Could someone tell me how to do the replacement in this
string with more items? Thank you.

Mike
Click to expand...
Click to expand...
 
H

H. Druss

cyberdude said:
Hi,

I have a string like this:

"A, B, C"

and I need to turn it to

"A, B and C"

, could someone tell me how to do the replacement? In some cases,
there are more than three items in the string (for example, "A, B, C,
D, E, F") Could someone tell me how to do the replacement in this
string with more items? Thank you.

Mike
Click to expand...

Hi Mike
Another sugestion.

Sub ReplaceLastComma()
Dim s As String
Dim i As Long
s = "A, B, C, D, E"
i = InStrRev(s, ",")
If i > 0 Then
s = Left(s, i - 1) & " and " & Mid$(s, i + 1)
MsgBox s
Else
MsgBox "no commas"
End If
End Sub

Good luck
Harold
 
D

Doug Robbins - Word MVP

Use

Dim s As String
Dim i As Long
s = Selection.Text
i = InStrRev(s, ", ")
Selection.Text = Left(s, i - 1) & " and" & Right(s, Len(s) - i)


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
 
C

cyberdude

Hi,

Thank you for all the replies.

Doug, you wrote in your code

s=Selection.Text
.

Since my string is "A, B, C", how can I make "Selection.Text" be "A,
B, C"? I think Selection.Text should contain "A, B, C", right? Thanks.

Mike
 
T

Tony Jollans

The question asked about a string, not the Selection that seems to be being
assumed. Regardless of that, running through the split array seems overly
complex. Why not. more simply:

Sub Demo2()
Dim StrArry
Dim strTxt As String
strTxt = "A, B, C" ' or use the Selection if you want
StrArry = Split(strTxt)
StrArry(UBound(StrArry)) = "and " & StrArry(UBound(StrArry))
strTxt = Join(StrArry)
MsgBox strTxt
End Sub

--
Enjoy,
Tony

www.WordArticles.com

macropod said:
Taking a leaf out of Helmut's book:
Sub Demo()
Dim i As Integer
Dim StrArry()
Dim strTxt As String
If InStr(Selection, "and") = 0 And InStr(Selection, ",") > 0 Then
For i = 0 To UBound(Split(Selection, ",")) - 1
ReDim Preserve StrArry(i)
StrArry(i) = Split(Selection, ",")(i)
strTxt = Join(StrArry(), ",")
Next
MsgBox strTxt & " and " & Split(Selection, ",")(UBound(Split(Selection,
",")))
End If
End Sub

--
Cheers
macropod
[MVP - Microsoft Word]

PS: to replace the string in the document, simply change 'MsgBox' to
'Selection.TypeText'


macropod said:
Hi cyberdude,

Here's one way:
Sub Demo()
Dim i As Integer
Dim StrArry()
Dim strTxt As String
With Selection
For i = 0 To UBound(Split(Selection, ",")) - 1
ReDim Preserve StrArry(i)
StrArry(i) = Split(Selection, ",")(i)
strTxt = Join(StrArry(), ",")
Next
MsgBox strTxt & " and " & Split(Selection, ",")(UBound(Split(Selection,
",")))
End With
End Sub

--
Cheers
macropod
[MVP - Microsoft Word]


cyberdude said:
Hi,

I have a string like this:

"A, B, C"

and I need to turn it to

"A, B and C"

, could someone tell me how to do the replacement? In some cases,
there are more than three items in the string (for example, "A, B, C,
D, E, F") Could someone tell me how to do the replacement in this
string with more items? Thank you.

Mike
Click to expand...
Click to expand...
Click to expand...
 
D

Doug Robbins - Word MVP

Yes, if you have A, B, C in your document and you select it and then run the
code, it will be replaced by A, B and C

Or, if the string is not actually in the document, but generated in some
other way, then use

s = "your comma separated string"

for example s = "A, B, C"

Then if you continue with the code as follows:

i = InStrRev(s, ", ")
s = Left(s, i - 1) & " and" & Right(s, Len(s) - i)

s will then contain the string "A, B and C"




--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Hi,

Thank you for all the replies.

Doug, you wrote in your code

s=Selection.Text
..

Since my string is "A, B, C", how can I make "Selection.Text" be "A,
B, C"? I think Selection.Text should contain "A, B, C", right? Thanks.

Mike
 
G

Graham Mayor

Why not? This simpler version leaves the comma before 'and' in place, which
is grammatically incorrect?
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Tony said:
The question asked about a string, not the Selection that seems to be
being assumed. Regardless of that, running through the split array
seems overly complex. Why not. more simply:

Sub Demo2()
Dim StrArry
Dim strTxt As String
strTxt = "A, B, C" ' or use the Selection if you want
StrArry = Split(strTxt)
StrArry(UBound(StrArry)) = "and " & StrArry(UBound(StrArry))
strTxt = Join(StrArry)
MsgBox strTxt
End Sub


macropod said:
Taking a leaf out of Helmut's book:
Sub Demo()
Dim i As Integer
Dim StrArry()
Dim strTxt As String
If InStr(Selection, "and") = 0 And InStr(Selection, ",") > 0 Then
For i = 0 To UBound(Split(Selection, ",")) - 1
ReDim Preserve StrArry(i)
StrArry(i) = Split(Selection, ",")(i)
strTxt = Join(StrArry(), ",")
Next
MsgBox strTxt & " and " & Split(Selection,
",")(UBound(Split(Selection, ",")))
End If
End Sub

--
Cheers
macropod
[MVP - Microsoft Word]

PS: to replace the string in the document, simply change 'MsgBox' to
'Selection.TypeText'


macropod said:
Hi cyberdude,

Here's one way:
Sub Demo()
Dim i As Integer
Dim StrArry()
Dim strTxt As String
With Selection
For i = 0 To UBound(Split(Selection, ",")) - 1
ReDim Preserve StrArry(i)
StrArry(i) = Split(Selection, ",")(i)
strTxt = Join(StrArry(), ",")
Next
MsgBox strTxt & " and " & Split(Selection,
",")(UBound(Split(Selection, ",")))
End With
End Sub

--
Cheers
macropod
[MVP - Microsoft Word]


Hi,

I have a string like this:

"A, B, C"

and I need to turn it to

"A, B and C"

, could someone tell me how to do the replacement? In some cases,
there are more than three items in the string (for example, "A, B,
C, D, E, F") Could someone tell me how to do the replacement in
this string with more items? Thank you.

Mike
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
VBA word change keys 0
Search and Replace patterns of strings with a RULE with any new message received. 0
Replace part of a string by another string 2
Function or Sub to help with text string 5
how can I use Word find and Replace to search inside a stringvariable (in a macro) 1
List items based on the count given 4
If/Then Merge with bullet points, but without blank lines 1

Top