Lesson 3: Text is data too
Imported CSV files, names, product codes, and addresses often contain inconsistent spacing or mixed formatting. These functions return text unless noted otherwise. In the examples, A2 contains " North-042 ".
Text formula reference
24 formulas shown
| Function | Purpose | Example and result |
|---|---|---|
| TRIM | Removes leading/trailing spaces and reduces repeated ASCII spaces to one. | =TRIM(A2) returns North-042. |
| CLEAN | Removes non-printing characters from imported text. | =CLEAN(A2) removes control characters. |
| UPPER | Converts letters to uppercase. | =UPPER("north") returns NORTH. |
| LOWER | Converts letters to lowercase. | =LOWER("NORTH") returns north. |
| PROPER | Capitalizes the first letter of each word. | =PROPER("ada lovelace") returns Ada Lovelace. |
| LEFT | Returns characters from the start. | =LEFT("North-042",5) returns North. |
| RIGHT | Returns characters from the end. | =RIGHT("North-042",3) returns 042. |
| MID | Returns characters from a position. | =MID("North-042",7,3) returns 042. |
| LEN | Counts characters, including spaces. | =LEN("North-042") returns 9. |
| FIND | Finds text and is case-sensitive. | =FIND("-","North-042") returns 6. |
| SEARCH | Finds text and is not case-sensitive; wildcards are supported. | =SEARCH("north",A2) returns 3 when A2 has two leading spaces. |
| SUBSTITUTE | Replaces matching text; optional instance selects one occurrence. | =SUBSTITUTE("1-2-3","-","/") returns 1/2/3. |
| REPLACE | Replaces text by character position. | =REPLACE("ABC-001",5,3,"245") returns ABC-245. |
| CONCAT | Joins text values without a delimiter. | =CONCAT(A2,"-",B2) joins two cells. |
| CONCATENATE | Legacy compatible text joining function. | =CONCATENATE(A2," ",B2). |
| TEXTJOIN | Joins a range with a delimiter and optional blank skipping. | =TEXTJOIN(", ",TRUE,A2:A5). |
| TEXT | Formats a number as text using a number format. | =TEXT(1234.5,"$#,##0.00") returns $1,234.50. |
| VALUE | Converts number-like text to a number using locale rules. | =VALUE("125.5") returns 125.5. |
| NUMBERVALUE | Converts text to a number with explicit decimal/group separators. | =NUMBERVALUE("1.234,5",",",".") returns 1234.5. |
| EXACT | Compares text with case sensitivity. | =EXACT("Excel","EXCEL") returns FALSE. |
| REPT | Repeats text a specified number of times. | =REPT("0",3) returns 000. |
| CHAR / CODE | Converts between numbers and characters in the current code page. | =CHAR(65) returns A; =CODE("A") returns 65. |
| UNICHAR / UNICODE | Converts Unicode code points and characters. | =UNICHAR(9733) returns a star; =UNICODE("A") returns 65. |
| TEXTBEFORE / TEXTAFTER | Returns text before or after a delimiter (Microsoft 365). | =TEXTBEFORE("North-042","-") returns North. |
Clean imported text: =TRIM(CLEAN(A2))
Split and rebuild a value
For older Excel, combine LEFT, MID, RIGHT, FIND, and LEN. In Microsoft 365, TEXTSPLIT can split a value across columns or rows; see Lesson 9 for dynamic-array formulas. Formatting numbers with TEXT creates text, so keep the original numeric value for calculations.
- Clean spaces and line breaks first.
- Find a delimiter rather than assuming a fixed position when source data varies.
- Check whether a result must remain numeric, especially dates and amounts.
Progress stays in this browser.