Bulk Insert and Remove Spaces in Excel: TRIM, SUBSTITUTE, CLEAN, and Power Query

Bulk space processing in Excel is the first step in cleaning data. This guide covers practical patterns in order, from basic functions such as TRIM/SUBSTITUTE/CLEAN to TEXTSPLIT/TEXTJOIN, LET/SEQUENCE (365), and bulk source-data transformations with Power Query.

Quick Fix

① Remove Spaces in Bulk

1. Trim leading/trailing spaces and reduce duplicate spaces to one:
   =TRIM(A2)
2. Also remove nonbreaking spaces (CHAR(160)), which are common in web copies:
   =TRIM(SUBSTITUTE(A2,CHAR(160)," "))
3. Also remove tabs and line breaks:
   =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
4. Remove all spaces without leaving any:
   =SUBSTITUTE(SUBSTITUTE(A2,CHAR(160),"")," ","")

② Insert Spaces in Bulk

1. Add one space after a delimiter, such as a comma:
   =SUBSTITUTE(A2,",",", ")
2. Insert a space every N characters, such as every 3 characters:
   =LET(s,A2,n,3,k,SEQUENCE(ROUNDUP(LEN(s)/n,0)),TEXTJOIN(" ",,MID(s,(k-1)*n+1,n)))
3. Insert a space at letter-number boundaries (365):
   =LET(s,A2, L,LEN(s), ch,MID(s,SEQUENCE(L),1), flags, (ch>="0")*(ch<="9"), prev, DROP(flags,-1), cur, DROP(flags,1), cut, VSTACK(FALSE, (prev=1)*(cur=0) + (prev=0)*(cur=1)), TEXTJOIN("",,IF(cut," "&ch, ch)))
   Meaning: Inserts one space only where numbers and letters change. (For example, AB12C → AB 12 C)

③ To Change the Source Data Directly Without Formulas

• Find and Replace (Ctrl+H) :
  • Remove all spaces → Find what:   (one space), Replace with: blank. Repeat as needed.
  • Add one space after a delimiter → Find what: ,, Replace with: , (comma plus space).
• Power Query (Data > From Table/Range): Transform > Format > Trim, Clean → Use Replace Values to insert a space after a specific character (advanced: Custom Column).

Why This Happens: Concepts and Principles

• Types of spaces: Standard space (32), nonbreaking space (160), tab (9), and line break (10/13). Because TRIM handles only character 32, data copied from the web or ERP systems requires replacing CHAR(160).
• Preserving source data vs. editing directly: Formulas are safe and easy to reverse. To modify source data directly, use Power Query or Find and Replace.
• When you convert a range to a table (Structured Reference)

Practical Examples

Sample Data

1) Clean Up Leading, Trailing, and Duplicate Spaces

Column B: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) → Result: Kim Seongjin

2) Keep Only Numbers or Letters (Optional)

• Numbers only: =TEXTJOIN("",,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))
• Letters only: =TEXTJOIN("",,IF(ISTEXT(MID(A2,SEQUENCE(LEN(A2)),1)),MID(A2,SEQUENCE(LEN(A2)),1),""))

3) Insert a Space Every N Characters

=LET(s,A2,n,3,k,SEQUENCE(ROUNDUP(LEN(s)/n,0)),
TEXTJOIN(" ",,MID(s,(k-1)*n+1,n)))

Example: ABCDEF → ABC DEF (n=3)

4) Add a Space After a Delimiter

=SUBSTITUTE(A2,",",", ") → 2025-11-12, Seoul, Korea

5) Insert Spaces at Letter-Number Boundaries (365)

=LET(s,A2, L,LEN(s), ch,MID(s,SEQUENCE(L),1),
num,(ch>="0")*(ch<="9"),
br, VSTACK(FALSE, num<>DROP(num,1)),
TEXTJOIN("",,IF(br," "&ch,ch)))

AB12C → AB 12 C

6) Apply to a Table (Structured Reference)

Convert the range to a table (Ctrl+T). If its name is tbl and the column is named [Raw]:

=TRIM(SUBSTITUTE(tbl[Raw],CHAR(160)," "))

The formula automatically expands when new rows are added.

7) Bulk Cleanup with Power Query

1. Data → From Table/Range → Open Power Query
2. Transform > Format: Apply Trim and Clean
3. Replace Values: Replace , with , to add a space after commas
4. Use Close & Load to apply the changes to the worksheet (keeps the source connection and can be refreshed)

Alternatives, Precautions, and Checklist

• Data copied from the web or ERP systems may contain nonbreaking spaces (160) → Use SUBSTITUTE(…,CHAR(160)," ") before TRIM.
• For phone numbers and ZIP codes, remove all spaces to improve comparison and join accuracy.
• For report display, add spaces after delimiters to improve readability.
• For batch changes, Power Query is recommended because it is easy to reuse and reverse.
• To convert formula results to values, copy the range → Paste Values.

Troubleshooting

Related Articles

• Complete Guide to Worksheet Shortcuts
• Reduce Excel File Size
• Advanced SUMPRODUCT Patterns
• Calculate Visible Rows with SUBTOTAL
• VLOOKUP vs. XLOOKUP