0

I have used an Excel formula =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))) to trim and clean extra spaces in multiple paragraphs of text. The formula worked, but it also removed spaces (some, but not all) between the end of a sentence and the beginning of the sentence that follows. I need to add a space between sentences that no longer have a space between them.

Example: This is the first sentence with a period ending the sentence, but no space after the period.This is the beginning of the second sentence. This is the beginning of the third sentence with proper space at the beginning, but missing a space at the end.And so on...

I found a suggestion to use the following code, but not sure how to utilize in Excel VBA.

To insert a space after every dot except for the dots that already have a space or dots at the end of the string:

var newString = Regex.Replace(oldString, @".(?! |$)", ". ");

Can the above code be used in Excel VBA to resolve this issue?

LAW
  • 1
  • 1
  • It is the same in VBA, declare the `RegExp` object and use `.Replace` with your `\.(?! |$)` pattern and `". "` replacement. – Wiktor Stribiżew Feb 26 '21 at 09:28
  • Thank you for answering my post. I have just started learning about VBA code and this is my first attempt. I am struggling through this and not sure where I am going wrong. I have tried to write the code that will provide a solution to the question above. My first attempt is as follows: – LAW Feb 28 '21 at 10:11
  • Sub RegExpReplace() Dim RegExp As Object Set RegExp = Nothing Set RegExp = CreateObject("VBScript.RegExp") Dim StrPattern As String: StrPattern = "\.(?! |$)" Dim StrReplace As String: StrReplace = "\.(?! |$)"". " Dim StrInput As String Dim StrOutput As String Set StrOutput = RegExp.Replace(StrInput, "\.(?! |$)", ". ") Dim MyCell As Range Set MyCell = Cells.ActiveSheet.Range("AG17:AG232") Dim MyRange As Range Set MyRange = ActiveSheet.Range("AG17:AG232") – LAW Feb 28 '21 at 10:23
  • With RegExp .Global = True .MultiLine = True .IgnoreCase = False .Pattern = StrPattern End With For Each MyCell In MyRange.Cells If StrPattern <> "" Then StrInput = MyCell.Value 'StrInput = MyRange.Value If RegExp.Test(StrInput) Then MsgBox MyCell & " Pattern Matched in Cell " & MyCell.Address & " " & RegExp.Replace(StrInput, StrReplace) 'StrOutput = RegExp.Replace(StrInput, "\.(?! |$)", ". ") Else MsgBox ("Pattern Not Matched") End If End If Next End Sub – LAW Feb 28 '21 at 10:42
  • Please add the code to the *question*, explain what you get and what you expect to get. – Wiktor Stribiżew Feb 28 '21 at 13:09

0 Answers0