[VBA] How to Bulk Delete Spaces in Strings | Using the Replace Method

Overview

When handling string data in Excel, you may encounter a mixture of word-level or unnecessary spaces (blanks). These spaces can cause issues such as search failures or inaccurate data aggregation.

In this article, I will introduce a method to bulk delete all half-width spaces from strings within cells using VBA. By utilizing the Replace method, you can efficiently process data across multiple cells.

---

Sample Code

Sub RemoveAllSpacesInText()

    ' Specify the range and replace half-width spaces with nothing
    Range("E3:E5").Replace _
        What:=" ", _
        Replacement:="", _
        LookAt:=xlPart, _
        MatchByte:=False

End Sub

---

Code Explanation

• Range(“E3:E5”): This is the cell range targeted for space removal. It can handle strings spanning multiple rows.
• What:=” “: Specifies that the target for deletion is a half-width space. To delete full-width spaces, specify "　".
• Replacement:=””: Replaces the space with an empty string (nothing). This effectively deletes the space.
• LookAt:=xlPart: Specifies that the replacement should occur if any part of the cell matches. This allows the macro to target spaces located anywhere within a string.
• MatchByte:=False: Searches without distinguishing between full-width and half-width characters (although since What:=" " is a half-width space, it specifically targets half-width in this context).

---

Before and After Comparison

Before Execution	After Execution
“Tokyo City”	“TokyoCity”
“Osaka　Ward”	“Osaka　Ward” (*Full-width space remains)
“N a g o y a”	“Nagoya”

*Note: If you also want to delete full-width spaces, you must add a separate Replace line with What:="　".

---

Application Examples

• Cleaning up unnecessary spaces in name or address data.
• Formatting product names, model numbers, or codes.
• Cleaning data pasted from CSV files or the web.

---

Notes

• Full-width spaces: These are not deleted by the sample code above. Use the following together if necessary:Range(“E3:E5″).Replace What:=”　”, Replacement:=””, LookAt:=xlPart, MatchByte:=False
• Formulas: This may affect cells containing formulas, so it is recommended to clearly define the target range.

---

Summary

In this article, I explained how to bulk delete spaces in strings using the VBA Replace method.

Deletion Target	Code Example
Half-width space	What:=" "
Full-width space	What:="　"
Both	Perform both replacements sequentially.

Removing unnecessary spaces is crucial for data formatting and high-precision processing. Please use this technique for pre-processing business data or creating reports.