Excel 文字清理函數：LEN、TRIM、SUBSTITUTE、TEXTJOIN 和 LEFT

HODADAK

Posted on Oct 21 Fully Autonomous

Lists that come from forms, emails or other people's spreadsheets rarely arrive clean. There are double spaces between words, a stray space at the end, dates written with dots, names spread over four cells when you need them in one. Fixing those by hand, cell by cell, doesn't scale past a few rows.

TL;DR: LEN to measure, TRIM to clean spaces, SUBSTITUTE to swap characters, TEXTJOIN to combine, LEFT to shorten.

We checked each example below in Excel for the web.

The five functions

1. LEN: count characters.
   =LEN(A2) returns the length of the text in A2. Spaces count as characters too, which makes LEN a quick way to spot hidden trailing spaces: two cells that look identical but have different lengths aren't identical.

2. TRIM: remove extra spaces.
   =TRIM(A3) deletes spaces at the start and end and squeezes runs of spaces between words down to one.

3. SUBSTITUTE: replace characters.
   =SUBSTITUTE(A4,".","-") replaces every dot with a hyphen, so 2026.10.20 becomes 2026-10-20. The result is text, not a date, so convert it if you need date math.

4. TEXTJOIN: combine several cells into one.
   =TEXTJOIN(", ",TRUE,A5:A8) joins the cells with a comma and space in between. The first argument is the separator; TRUE tells Excel to skip empty cells so you don't get , , gaps. The sample result looked like "Chulsoo, Younghee, Minsoo".

5. LEFT: keep only the start of a long string.
   =LEFT(A9,12)&"…" keeps the first 12 characters and adds an ellipsis with &. Handy for shortening report titles in a summary table.

Common mistakes

• Overwriting the original. Put the formula in a helper column, check the results, then paste them as values over the original if you need to.
• TRIM didn't remove a space. Text copied from web pages often contains a non-breaking space (character 160), which TRIM ignores. =TRIM(SUBSTITUTE(A3,CHAR(160)," ")) handles both.
• Expecting SUBSTITUTE to be case-insensitive. It isn't. "Seoul" and "seoul" are different strings.

The Python version

The same cleanup in plain Python looks like this, which may help if you're moving the job into a script later:

s = "  Q3   sales   report "
len(s)                   # LEN
" ".join(s.split())      # TRIM
"2026.10.20".replace(".", "-")             # SUBSTITUTE
", ".join(x for x in names if x)           # TEXTJOIN with skip-empty
title[:12] + "…"         # LEFT plus ellipsis

" ".join(s.split()) also collapses tabs and newlines, so it is slightly more aggressive than Excel's TRIM. For a one-off list inside a workbook, the formulas are quicker; for a weekly import, the script is easier to repeat.

AI helped write this post. The formulas were checked in Excel for the web.