We help IT Professionals succeed at work.

Replacing comma with comma space

Ravindra_Nag
Ravindra_Nag asked
on

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.
Comment
Watch Question

GrahamSkanRetired
Top Expert 2012

Commented:
Try a Wildcard Find and replace

Find: ([A-z]{1,})\,([A-z0-9]{1,})
Replace: \1, \2

Author

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
GrahamSkanRetired
Top Expert 2012

Commented:
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

Author

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.

Commented:
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
GrahamSkanRetired
Top Expert 2012

Commented:
Absolutely. The sixth replacement does it unconditionally.

Author

Commented:
Keyu / Graham,
I am not good at understanding VB. Can you please provide the same in a complete macro script.

Thanks
GrahamSkanRetired
Top Expert 2012

Commented:
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

Author

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
Retired
Top Expert 2012
Commented:
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

Author

Commented:
Thank you so much.