Fastest way to remove extra blank space - in VBA excel

Question

I'm searching the fastest way to remove [multiple] blank spaces in a string, to output in a cell afterwards. There can be 1, 2 3, X space characters.

*example: "hello  world                         how are    you"*

My process is quite time consuming so i am looking for the fastest way, not necessary the easiest, but my knowledge in vba is limited.

Here is what i found so far:

Regex Removing blank space from a string 'this one is not working for me, even if i add the regex ref. i got a compilation error.

With New RegExp
        .Pattern = "\s+"
        .Global = True
        RemoveExtraSpace = .Replace(inVal, " ")
    End With

WorksheetFunction

Application.WorksheetFunction.Trim(s)

EDIT: str = Application.WorksheetFunction.Trim(str) 'seems to work

a for each loop I think this one splits, then reconstruct with one space between each word?

str = Split(s)
s = ""
For Each str1 In str
    If str1 <> "" Then
        s = s & str1 & " "
    End If
Next str1
s = Trim(s)

This one talks about removing ALL spaces

So i am quite confused. I feel like i should use the regEx. Anyone want to share their thoughts on this? Thank you!

[This](https://stackoverflow.com/a/58454080/9758194) may be helpfull. — JvdV, Mar 01 '21 at 21:24
Thank you JvdV. I did test WorksheetFunction again, and forgot to return the value to the updated cell value. Now it's working. i will apply it to my whole project, hoping it is not too much time consuming. — Sophie Dionne, Mar 01 '21 at 21:52

score 0 · Answer 1 · answered Mar 01 '21 at 21:44

I hope got your idea

write the text as is, in the destination cell and then replace using one of those codes

Sub ReplaceInColumn()
    Columns("C:C").Select ' change your columns as you need
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

Sub ReplaceInCell()
    Range("A2").Select 'chage your cell as you need
    Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
        FormulaVersion:=xlReplaceFormula2
End Sub

Yes, It will remove all spaces in the cell or column or range as you adapt the code you can replace the cell with activecell — Miguel Angel Cortez Rodriguez, Mar 01 '21 at 22:28

score 0 · Answer 2 · answered Mar 02 '21 at 17:14

0

if you need do it by code, just try this

Sub ReplaceSpaces()
    Dim myWord_ As String
    myWord_ = "hello  world                         how are    you"
    myWord_ = Replace(myWord_, " ", "")
End Sub

answered Mar 02 '21 at 17:14

Miguel Angel Cortez Rodriguez

119
3

Fastest way to remove extra blank space - in VBA excel

2 Answers2