UPPER / LOWER / PROPER / FIND / TEXTJOIN
Five more everyday text functions: three for changing letter case, one for finding a case-sensitive position, and one for joining a list with a shared delimiter.
UPPER, LOWER, and PROPER are the standardize-this-column workhorses: useful for comparing text that was typed inconsistently across a sheet, or for formatting a name correctly for display. FIND's case sensitivity is its one real difference from SEARCH, covered on the main text functions page: "Code" and "code" are not the same match to FIND, where SEARCH would treat them as equal.
TEXTJOIN replaces a long chain of &","&cell&","&cell with a single call that handles both the delimiter and any blank cells for you, which is a real improvement once more than two or three values need joining together.
Syntax
| Argument | Description |
|---|---|
| text | UPPER/LOWER/PROPER: Required. The text to convert: all caps, all lowercase, or Title Case. |
| find_text, within_text | FIND: Required. The text to search for, and the text to search inside. Unlike SEARCH, FIND is case-sensitive. |
| delimiter | TEXTJOIN: Required. The text to insert between each piece, e.g. ", ". |
| ignore_empty | TEXTJOIN: Required. TRUE skips blank cells instead of adding an extra delimiter for them. |
| text1, [text2], ... | TEXTJOIN: Required. Up to 252 values, cells, or ranges to join in order. |
A real example: joining a short list into one readable string
Three meeting days in separate cells, joined into one sentence-ready string:
Setting ignore_empty to TRUE matters most once the range has gaps: without it, every blank cell in A1:A3 would add its own extra comma to the output instead of being skipped over cleanly.
Worked examples
| A | |
|---|---|
| 1 | Code |
| 2 | sku-100 |
| A | |
|---|---|
| 1 | Text |
| 2 | Error Code: 404 |
| A | |
|---|---|
| 1 | Mon |
| 2 | Wed |
| 3 | Fri |
Common mistakes
- Using FIND when the search shouldn't care about case. SEARCH ignores case; FIND does not: picking the wrong one silently returns #VALUE! on a near-miss.
- Forgetting TEXTJOIN's ignore_empty argument, which produces a run of extra delimiters for every blank cell in the range when left FALSE.
- Reaching for PROPER on text that's meant to stay as-is, like an acronym or a SKU; PROPER capitalizes every word, which can mangle anything that isn't a normal name.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice text helpers →