Clean and combine text
Make imported labels consistent without destroying meaningful identifiers.
By the end, you’ll be able to:
- Clean ordinary spaces and inspect string length
- Extract fixed-position code segments
- Combine fields with deliberate separators
Keep the original while you clean
Names and product codes often arrive with inconsistent spacing. Keep a raw column, add a cleaned output and compare examples before replacing the original. A formula cannot decide whether two similar names refer to the same person.
The fictional code SG-0149 has a country prefix, a hyphen and a four-character identifier. Preserve the identifier as text: removing its leading zero changes the code’s presentation and can break a downstream match.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Raw name | Code | First | Last | Middle |
| 2 | Maya Chen | SG-0149 | Maya | Chen | |
| 3 | Leo Park | UK-0208 | Leo | Park |
Trim spaces, measure and standardize case
=TRIM(text)=LEN(text)=UPPER(text)=LOWER(text)text is the source string or cell. TRIM removes leading/trailing ordinary spaces and reduces repeated ordinary spaces between words to one. LEN counts characters, including spaces. UPPER and LOWER change letter case.
=TRIM(A2) returns Maya Chen, and =LEN(TRIM(A2)) returns 9. Excel TRIM does not remove every Unicode whitespace character; nonbreaking spaces from web pages can need additional cleaning. Test a real imported sample.
Extract a segment by position
=LEFT(text, [num_chars])=RIGHT(text, [num_chars])=MID(text, start_num, num_chars)text is the source. num_chars is how many characters to return; LEFT/RIGHT default to 1 when omitted. MID’s required start_num counts from 1, and its required num_chars gives the segment length.
For B2, =LEFT(B2,2) returns SG, =RIGHT(B2,4) returns 0149, and =MID(B2,4,4) also returns 0149. Fixed positions are reliable only when the code structure is reliable.
Join fields without accidental extra separators
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)=CONCAT(text1, [text2], ...)delimiter is text inserted between items; ignore_empty controls whether empty cells are skipped; text1 and further text arguments supply cells, ranges or strings. CONCAT joins supplied text without adding a separator automatically.
=TEXTJOIN(" ",TRUE,C2,E2,D2) returns Maya Chen, skipping the empty middle name. =CONCAT(C2," ",D2) produces the same result using an explicit space. Excel’s & operator can also join text.
Check structure rather than assuming it
Try a blank name, an extra space and a code from another country. If codes vary in length or delimiter position, fixed MID arguments can silently extract the wrong segment. Keep a validation flag for unexpected structures and investigate those rows.
LEFT, RIGHT, MID, LEN, TRIM, UPPER and LOWER work in older Excel versions. TEXTJOIN and CONCAT are available in Microsoft 365, Excel 2019 and later and current web Excel. The browser tasks use ordinary English characters; complex Unicode character counting can differ with Excel compatibility settings.
Give it a try.
Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.
0 of 6 exercises complete
The bounded practice checker accepts equivalent supported formulas, lowercase function names and text labels, extra spaces, and comma or semicolon separators. Array tasks check the complete result shape and values. It is a teaching tool, not a complete Excel engine. See supported behavior.
Take this with you
Keep raw data, clean deliberately and preserve identifiers as text. Extraction positions must match a verified structure.
Practice in Excel
Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.
Download practice workbook Check my workbook ↗Technical references and lesson review
Original lesson content. Technical behavior checked against Microsoft documentation. Reviewed 2026-10-16.