Excel 365 - Search and replace all instances of two spaces until none are found 2

[MattGreer]

I have an Excel file that's an export from an Adobe PDF. It's all I have to work with. In order to clean it up, I need to replace all instances of multiple spaces with just one space. I typically do this by doing "search: (two spaces) replace: (one space)" multiple times until the search result tells me no instances of double spaces are found.

(edit: I need to do a search and replace in the spreadsheet, all over the spreadsheet, until no double spaces are found.)

Is there a way to automate this in VBA? If so, how?

I found an example of a Do loop, but it's just for searching, not replacing, so not sure how to set it up or even if this is the right solution.

Do

code stuff here

Loop While Not (Search Result) is Nothing

Thank you for your help!



Thanks!!


Matt

 

[Andrzejek]

Private Sub ReplaceSpaces()
Dim str As String
[blue]
str = "This is  my    string   with   some   spaces     in     it"
[/blue]
Do Until InStr(str, Space(2)) = 0
    str = Replace(str, Space(2), Space(1))
Loop

Debug.Print str

End Sub

You are going to get:[tt]
This is my string with some spaces in it[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MattGreer]

Nice going!

I managed to find something that works just this moment. This is what I came up with. Do you see any potential issues?

For what it's worth I like your code better. Nice and concise, but it doesn't search through a spreadsheet, just a variable?

Public Sub EliminateSpaces()

Dim searchResult

Do
    Set searchResult = Cells.Find(What:="  ")
    
    If Not searchResult Is Nothing Then Cells.Replace What:="  ", Replacement:=" "

Loop While Not searchResult Is Nothing


End Sub

Thanks!!


Matt

 

[Andrzejek]

Your code deals with all cells in a worksheet, which is nice.
With mine, you would have to loop thru all cells with text in additional code.

Choose your poison...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MattGreer]

Actually, the next step for me is to start searching in cell values for a certain bit of information, so your post has been quite helpful! Thanks!

Thanks!!


Matt

 

[strongm]

>I need to replace all instances of multiple spaces with just one space

You can do this somewhat more efficiently (no looping, no repeated rebuilding of strings) with a regular expression, e.g.

[blue]Private Sub ReplaceSpaces()
    Dim str As String
    
    str = "This is  my    string   with   some   spaces     in     it"
    
    With New RegExp
        .Global = True
        .Pattern = "\s+"
        str = .Replace(str, " ")
    End With
    
    Debug.Print str

End Sub[/blue]

 

[combo]

In excel vba only, Application.Trim will replace multiplicated spaces with single instances.

combo

 

[MattGreer]

Combo, should have given you a star before, thank you for that suggestion.

Thanks!!


Matt