Text cleaning and transformation

Excel Text Functions Guide

Clean, split, combine, and standardize text in Excel with TEXTJOIN, LEFT, RIGHT, MID, TRIM, CLEAN, and modern text formulas.

Text cleanup workflow

  • Use TRIM and CLEAN to remove extra spaces and hidden characters before matching data.
  • Use LEFT, RIGHT, MID, FIND, and SEARCH to extract parts of codes, names, or descriptions.
  • Use TEXTJOIN, CONCAT, and CONCATENATE to combine values into readable labels.

Modern text formulas

  • Use TEXTSPLIT when one cell contains values that should become separate columns or rows.
  • Use TEXTBEFORE and TEXTAFTER to extract values around a delimiter.
  • Use VALUE when a number is stored as text and must be used in calculations.

Practical text cleanup workflow

  • Run TRIM and CLEAN on imported CSV columns before any lookup or match.
  • Parse fixed-width codes with LEFT, MID, RIGHT; use FIND when delimiters vary row by row.
  • Rebuild display labels with TEXTJOIN and keep raw codes in separate columns for joins.
  • Spot-check parsed output against three known source strings before batch processing.

Recommended next steps

  • LEFT, RIGHT, and MID to parse codes, SKUs, and fixed-width identifiers from a single column.
  • TRIM and CLEAN before comparing text in lookups — invisible spaces cause most false #N/A results.
  • TEXTJOIN or CONCAT to rebuild full names, addresses, and export-ready labels from split columns.
  • Compare CONCAT vs TEXTJOIN when you need a delimiter between merged values.

Text formulas for messy exports

  • Extract email domain: =RIGHT(A2,LEN(A2)-FIND("@",A2)) after TRIM.
  • Normalize phone: =TEXTJOIN("",TRUE,LEFT(B2,3),MID(B2,5,3),RIGHT(B2,4)) when formats differ.
  • Replace legacy codes: =SUBSTITUTE(SKU,"OLD-","NEW-") across a migration column.
  • Proper case names: =PROPER(TRIM(C2)) before mail-merge export.

People also ask

  • Why does VLOOKUP fail on text that looks identical? Hidden spaces or different Unicode characters — use TRIM and EXACT to diagnose.
  • CONCAT vs TEXTJOIN? TEXTJOIN adds delimiters and can ignore blanks; CONCAT simply merges values.
  • When to use Power Query instead? When parsing rules change often or source files arrive in inconsistent formats.

Deep-dive function map for Excel Text Functions Guide

  • TEXTJOIN (/functions/textjoin/): Joins text from multiple ranges with a delimiter, with option to ignore empty cells. Example: =TEXTJOIN(", ", TRUE, A1:A10).
  • CONCAT (/functions/concat/): Joins two or more text strings into one string. Example: =CONCAT(A1, " ", B1).
  • LEFT (/functions/left/): Returns the specified number of characters from the start of a text string. Example: =LEFT("Hello World", 5).
  • RIGHT (/functions/right/): Returns the specified number of characters from the end of a text string. Example: =RIGHT("Hello World", 5).
  • MID (/functions/mid/): Returns a specific number of characters from a text string, starting at a specified position. Example: =MID("Hello World", 7, 5).
  • TRIM (/functions/trim/): Removes extra spaces from text, leaving only single spaces between words. Example: =TRIM(" Hello World ").
  • CLEAN (/functions/clean/): Removes all non-printable characters from text. Example: =CLEAN(A1).
  • TEXTSPLIT (/functions/textsplit/): Splits text into rows or columns using delimiters. Example: =TEXTSPLIT("a,b,c", ",").
  • TEXTBEFORE (/functions/textbefore/): Returns text that occurs before a specified delimiter. Example: =TEXTBEFORE("John Smith", " ").
  • TEXTAFTER (/functions/textafter/): Returns text that occurs after a specified delimiter. Example: =TEXTAFTER("John Smith", " ").
  • VALUE (/functions/value/): Converts a text string that represents a number to a number. Example: =VALUE("123").

Two-week study plan

  • Week 1 — Read each related function page and copy one example per function into a practice Table.
  • Week 2 — Apply this guide to a real export from your job; document fixes in a changelog tab.
  • Explore categories: Text.
  • Run the fix-excel-formula-errors checklist when any formula shows #N/A, #VALUE!, #REF!, or #SPILL!.
  • Compare similar functions (VLOOKUP vs XLOOKUP, SUMIF vs SUMIFS) before standardizing team templates.
  • Export a PDF snapshot of your completed practice workbook for future reference and onboarding.

Error and troubleshooting cross-links

  • Fix #VALUE! (/errors/value-error/) — step-by-step causes and solutions in the error directory.
  • Fix #NAME? (/errors/name?/) — step-by-step causes and solutions in the error directory.