What You'll Learn / Key Take-Aways

- How to change text case with UPPER, LOWER, and PROPER.

- How TRIM fixes extra spaces and when to use CLEAN.

- Two ways to join text: CONCATENATE (and CONCAT) and the ampersand operator.

- How TEXT and VALUE convert between numbers and formatted text.

- How to extract substrings with LEFT, MID, and RIGHT, and how to combine them with SEARCH or FIND.

- When to use SEARCH versus FIND and common troubleshooting tips.

## Why text functions matter

Text data shows up everywhere. Names, addresses, product codes, and imported reports often need cleaning and reformatting. Text functions let you standardize case, remove unwanted characters, combine fields, and extract the pieces you need. The patterns here are the same ones I use in real reports to get data ready for analysis.

## Basic case and whitespace functions

### UPPER, LOWER, PROPER

- UPPER(text): converts text to all uppercase.

- LOWER(text): converts text to all lowercase.

- PROPER(text): capitalizes the first letter of each word and makes the rest lowercase.

Examples

- =UPPER(A2) turns "john smith" into "JOHN SMITH".

- =PROPER(A2) turns "JOHN SMITH" into "John Smith".

Use PROPER for display names, UPPER when you need consistent matching for codes, and LOWER when you are normalizing text for case-insensitive comparisons.

### TRIM and CLEAN

- TRIM(text): removes leading and trailing spaces and reduces repeated spaces between words to a single space.

- CLEAN(text): removes nonprintable characters often present when importing data from other systems.

Always run TRIM on imported names or addresses before splitting or matching. If you see odd empty characters, use CLEAN first then TRIM: =TRIM(CLEAN(A2)).

## Joining text: CONCATENATE, CONCAT, and the ampersand

- CONCATENATE(text1, text2, ...) joins multiple text values into one string. In newer Excel versions you can use CONCAT.

- Ampersand operator (&) is shorter and often clearer: =A2 & " " & B2 joins first and last name with a space.

Examples

- =CONCATENATE(A2, " ", B2)

- =A2 & " " & B2

When you need separators like commas or hyphens, include them as literal text: =A2 & ", " & B2.

## Converting between text and numeric formats: TEXT and VALUE

- TEXT(value, format_text): converts a number to text with a specific number format. Useful when you need a formatted string like a currency or a date. Example: =TEXT(A2, "mm/dd/yyyy") or =TEXT(B2, "$#,##0.00").

- VALUE(text): converts a text string that looks like a number back into a numeric value. Useful after you use TEXT or when numbers are stored as text.

Caution: TEXT returns text. If you want to use the result in calculations, convert it back with VALUE or avoid TEXT until display.

## Extracting pieces: LEFT, MID, RIGHT

- LEFT(text, n): returns the first n characters from the left.

- RIGHT(text, n): returns the last n characters from the right.

- MID(text, start_num, num_chars): returns num_chars starting at start_num.

Examples

- =LEFT(A2, 3) gets the first three characters of a product code.

- =RIGHT(A2, 4) gets the last four digits of a card number.

- =MID(A2, 4, 5) grabs five characters starting at position 4.

These functions are most powerful when combined with SEARCH or FIND to discover the position of a delimiter.

## Finding positions: SEARCH vs FIND

- SEARCH(find_text, within_text, [start_num]) finds the position of find_text within within_text and is case insensitive. It supports wildcard characters like ? and *.

- FIND(find_text, within_text, [start_num]) finds the position but is case sensitive and does not support wildcards.

Example: If you want the position of the at sign in an email, =SEARCH("@", A2) returns the index where the at sign appears.

## Common pattern: extract first name, last name, or domain

Extract first name from a full name in A2

- =LEFT(A2, SEARCH(" ", A2) - 1)

Extract last name when there are only two parts

- =RIGHT(A2, LEN(A2) - SEARCH(" ", A2))

Extract domain from an email in A2

- Start position of domain: =SEARCH("@", A2) + 1

- Domain: =MID(A2, SEARCH("@", A2) + 1, LEN(A2) - SEARCH("@", A2))

If emails have subfolders or parameters, combine SEARCH with FIND for additional delimiters.

## Putting it together: nested formulas and readability

You will often nest functions. For readability:

- Build formulas step by step in helper columns when you are learning.

- Use named ranges if the same expression repeats.

- Consider LET in newer Excel versions to store intermediate values.

Example: Clean and extract first name

=LEFT(TRIM(PROPER(A2)), SEARCH(" ", TRIM(PROPER(A2))) - 1)

This handles extra spaces and case, then extracts the first name.

## Additional Tips, Pitfalls to Avoid & Pro Advice

- Watch for invisible characters. If trimming fails, use CODE and CLEAN to inspect characters.

- Remember TEXT returns text. If you format numbers with TEXT for display, you cannot sum them until you wrap them with VALUE.

- Use SEARCH when you want case-insensitive matching. Use FIND when case matters.

- If you get #VALUE errors with SEARCH or FIND, wrap the call in IFERROR and provide a fallback. Example: =IFERROR(SEARCH("@", A2), 0).

- When joining ranges of cells by delimiter in Office 365, consider TEXTJOIN which is more flexible than CONCATENATE.

## FAQ

### How do I join first and last names into one cell?

Use CONCATENATE or the ampersand. Example: =A2 & " " & B2 or =CONCATENATE(A2, " ", B2).

### How do I extract the domain from an email address?

Find the at sign position with SEARCH("@", A2) then use MID. Example: =MID(A2, SEARCH("@", A2) + 1, LEN(A2) - SEARCH("@", A2)).

### What is the difference between FIND and SEARCH?

SEARCH is case insensitive and supports wildcards. FIND is case sensitive and does not support wildcards.

### How can I remove extra spaces from imported text?

Use TRIM to remove leading, trailing, and extra internal spaces. If characters still look wrong, use CLEAN first, then TRIM: =TRIM(CLEAN(A2)).

### How do I keep leading zeros when a column imports numbers as 123 instead of 00123?

Use TEXT to format the number as text with leading zeros: =TEXT(A2, "00000"). Keep in mind the result is text, not a numeric value.

### How do I convert numeric text to an actual number?

Wrap the text with VALUE or multiply by 1. Example: =VALUE(A2) or =A2*1.

## Summary of Key Points / Take-Home Messages

- Use UPPER, LOWER, and PROPER to standardize case.

- Use TRIM and CLEAN to remove unwanted spaces and characters.

- Use CONCATENATE or & to join strings; use TEXTJOIN in newer Excel versions for ranges.

- Use LEFT, MID, and RIGHT to extract substrings; use SEARCH or FIND to locate delimiters.

- Remember TEXT returns text and VALUE converts text back to numbers. Wrap functions in IFERROR when needed to avoid errors.

Put these patterns into practice on a copy of your data. Start with TRIM and PROPER to clean text, then extract or join as needed.