Replacing comma with comma space


Hello All,
I need to cleanse a file taken from Net repeatedly. Currently, I am working on the need to regulate the commas. I need to insert space after comma.
For exmaple, "test,test, test" should become "test, test, test"
I am able to do it with multiple find and replace as stated below (thanks to previous postings):
Dim ToFind, NewWord
    Dim i As Long
   
    ToFind = Array(";", ",  ")
    NewWord = Array(", ",",  ")
   
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    For i = 0 To UBound(ToFind)
        With Selection.Find
            .Text = ToFind(i)
            .Replacement.Text = NewWord(i)
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
        End With
        Selection.Find.Execute Replace:=wdReplaceAll
    Next
End Sub

However, there are 2 issues i have noticed and looking for solution.
1. Replacement in numbers to be avoided. For example 100,000 should not become 100, 000. However "test,5PM" should become "test, 5PM"
2. The replacement needs to happen only in the lines that have been selected.

Thanks in Advance.
Ravindra_NagAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
Try a Wildcard Find and replace

Find: ([A-z]{1,})\,([A-z0-9]{1,})
Replace: \1, \2
0
Ravindra_NagAuthor Commented:
I am finding it difficult to put the wildcard in the VBA code. However, I have tried the following replacement:
ToFind = Array("^p^p", ";", "  ", ".^p", " ^p", ",", " *([A-z]{1,})\,([A-z0-9]{1,})", "  ")
    NewWord = Array("^p", "; ", " ", "^p", "^p", ", ", "\1, \2", " ")

With this I am able to clear the paragraphs of double spaces, extra lines etc. However 10,000 is still becoming 10, 000 which i need to avoid.
Additionally, I also need to do this only for selected lines and not the entire document.

Thanks
0
GrahamSkanRetiredCommented:
You don't show how you are using the arrays.

Does this help?
Sub CommaSpace()
    Dim ToFind As Variant
    Dim NewWord As Variant
    Dim i As Integer
    
    ToFind = Array("^p^p", ";", "  ", ".^p", " ^p", ",", " *([A-z]{1,})\,([A-z0-9]{1,})", "  ")
    NewWord = Array("^p", "; ", " ", "^p", "^p", ", ", "\1, \2", " ")
    
    For i = 0 To UBound(ToFind)
        With ActiveDocument.Range.Find
            .Text = ToFind(i)
            .Replacement.Text = NewWord(i)
            .MatchWildcards = (i = 6)
            .Execute Replace:=wdReplaceAll
        End With
    Next i
End Sub

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Ravindra_NagAuthor Commented:
Even with this code incorporated 10,000 is getting converted to 10,000.
Is it due to to the other variables we have put in.
0
keyuCommented:
here you are replacing just one array value with another while you dont keep it so complicated

try like this

for i=0 to ubound(tarray)-1
tarray(i)=replace(replace(tarray(i),", ",","),",",", ")
next
0
GrahamSkanRetiredCommented:
Absolutely. The sixth replacement does it unconditionally.
0
Ravindra_NagAuthor Commented:
Keyu / Graham,
I am not good at understanding VB. Can you please provide the same in a complete macro script.

Thanks
0
GrahamSkanRetiredCommented:
OK, this removes the sixth term from your arrays which were find:  "," and replace: ", ". The card than needs to be applied to the sixth iteration (index 5).
Sub CommaSpace()
    Dim ToFind As Variant
    Dim NewWord As Variant
    Dim i As Integer
    
    ToFind = Array("^p^p", ";", "  ", ".^p", " ^p", , " *([A-z]{1,})\,([A-z0-9]{1,})", "  ")
    NewWord = Array("^p", "; ", " ", "^p", "^p",  "\1, \2", " ")
    
    For i = 0 To UBound(ToFind)
        With ActiveDocument.Range.Find
            .Text = ToFind(i)
            .Replacement.Text = NewWord(i)
            .MatchWildcards = (i = 6)
            .Execute Replace:=wdReplaceAll
        End With
    Next i
End Sub

Open in new window

0
Ravindra_NagAuthor Commented:
Keyu / Graham,
I am still facing the same issue. 10,000 is converted to 10, 000.
This is in addition to the point that entire document is being converted.

I have attached the test document I am using. it also contains the run time error screen shots.

Thanks
Test-ver2.doc
0
GrahamSkanRetiredCommented:
I have tweaked the code for the Wildcard replace item, which I somehow copied wrongly.
Also, the code now loops until there are no items to replace. Thus it will, say, replace three consecutive paragraph marks with one.
Finally, it now works on the Selection, and not on the whole document.

Note that the document has 10, 000 already, so that will stay as is. Also one picture on your document shows a Type Mismatch error which I can't reproduce.


Sub CommaSpace()
    Dim ToFind As Variant
    Dim NewWord As Variant
    Dim i As Integer
     
    ToFind = Array("^p^p", ";", "  ", ".^p", " ^p", "([A-z]{1,})\,([A-z0-9]{1,})", "  ")
    NewWord = Array("^p", "; ", " ", "^p", "^p", "\1, \2", " ")
    
    For i = 0 To UBound(ToFind)
        With Selection.Find
            .Text = ToFind(i)
            .Replacement.Text = NewWord(i)
            .MatchWildcards = (i = 5)
            Do While .Execute(Replace:=wdReplaceAll)
            Loop
        End With
    Next i
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ravindra_NagAuthor Commented:
Thank you so much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.