In Excel, string functions work with text, while operators combine values or compare them. The main text-joining operator is the ampersand (&); functions such as LEFT, FIND, SUBSTITUTE, and TEXTJOIN let you extract, locate, change, and assemble text for Microsoft 365 on Windows.
What “string” means in Excel
A string is a sequence of characters treated as text: a name, product code, address, or sentence. Excel calls this data type text. A cell can contain text that looks like a number, such as a postal code, as well as a numeric value that Excel can calculate with. The distinction matters: text “0042” preserves its leading zeros, while the numeric value 42 does not.
Text functions return text, a position, a count, or—in some cases—a number converted from text. They are useful for cleaning imported data, splitting or combining labels, standardizing capitalization, and preparing values for lookups or reports. Excel formulas can also join text directly with an operator, without calling a dedicated function.
String operators: join and compare
The ampersand joins values into one text result. With a first name in A2 and a surname in B2, enter =A2&" "&B2 in another cell to return a name with a space between the parts. The ampersand converts values as needed for the joined result; it does not preserve a number’s display format. For example, a date stored as a number can appear as its underlying serial value rather than as a formatted date.
Excel also uses comparison operators with text. The operators =, <>, <, >, <=, and >= return TRUE or FALSE when comparing values. For example, an equality check can test whether two text cells match. Ordinary text comparisons are not case-sensitive: “North” and “NORTH” are treated as equal. Use the EXACT function when capitalization must count.
Text concatenation has a place in Excel’s operator precedence: it is evaluated after addition and subtraction but before comparisons. When an expression mixes several operations, parentheses make the intended order clear. A common confusion is that the asterisk and question mark are not general string-comparison operators in Excel. They act as wildcards in certain functions and criteria, such as COUNTIF, while the tilde can escape a wildcard so it is matched literally.
Functions for extracting and measuring text
LEFT, RIGHT, and MID return portions of a text value. LEFT starts at the beginning, RIGHT at the end, and MID starts at a position you specify. LEN counts the characters in a value, including spaces. These functions are practical for fixed-format codes, but they rely on character positions; they do not understand the meaning or structure of a name or address.
For example, with a product code in A2, =LEFT(A2,3) returns its first three characters. If A2 contains “ABC-204,” the result is “ABC.” To return four characters starting at position five, use =MID(A2,5,4); for that same sample, the result is “204” because only three characters remain. The supplied length is a maximum, not a promise that every text value contains that many characters.
To count a value’s characters, with text in A2, use =LEN(A2). If A2 contains “red fox,” the result is 7, because the space counts too. This is useful for spotting unexpected spaces or checking whether an imported field meets a length rule.
Finding and replacing parts of a string
FIND and SEARCH return the position where one text fragment first appears inside another. FIND is case-sensitive; SEARCH is not. Both return an error if the requested text is absent, so a formula that depends on a match may need error handling. SEARCH can also use the * and ? wildcard characters; FIND treats those characters literally.
With an identifier in A2, use =FIND("-",A2) to locate its hyphen. If A2 contains “AB-204,” the result is 3. SUBSTITUTE replaces matching text, optionally only at a particular occurrence; REPLACE changes characters at a specified position. For example, with “AB-204” in A2, =SUBSTITUTE(A2,"-","/") returns “AB/204.” Use SUBSTITUTE when you know what text to change, and REPLACE when the position and length are what matter.
When a missing search term is a normal possibility, IFERROR can provide a readable alternative rather than showing an error. This should not conceal a problem that needs investigation, however. If the text is expected to contain a delimiter, an error may reveal inconsistent source data worth fixing.
Combining text: ampersand, CONCAT, and TEXTJOIN
The ampersand is convenient for short combinations and works in older Excel versions. CONCAT joins text values and ranges, and is the modern replacement for the older CONCATENATE function. TEXTJOIN adds a chosen delimiter and can optionally ignore empty cells, which makes it useful for lists with variable numbers of parts.
With names in A2 and B2, =CONCAT(A2," ",B2) joins them with one space. With items in A2:A5, =TEXTJOIN(", ",TRUE,A2:A5) joins nonblank items using a comma and a space; TRUE tells Excel to ignore empty cells. In Microsoft 365, TEXTJOIN can accept ranges and is available alongside CONCAT. If empty cells should create empty positions between delimiters, set the ignore-empty argument to FALSE instead.
For a small number of pieces, ampersands can be easiest to read. For a list, TEXTJOIN is generally clearer than writing a long chain of ampersands. Neither method automatically inserts punctuation or spaces: you must include those explicitly, or specify a delimiter in TEXTJOIN.
Cleaning text and changing capitalization
TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to one. CLEAN removes many nonprinting control characters that can arrive in copied or imported text. Neither function removes every character that may look like a space: in particular, a nonbreaking space can survive both. This explains why two values that look identical may fail to match.
With imported text in A2, =TRIM(CLEAN(A2)) applies both cleanup steps. UPPER changes letters to uppercase, LOWER to lowercase, and PROPER capitalizes the first letter of words. These are display transformations, not a substitute for deciding how names, acronyms, or product codes should be capitalized. PROPER, for instance, may not format every name or abbreviation the way a person expects.
To check whether two values match including letter case, with the values in A2 and B2, use =EXACT(A2,B2). It returns TRUE only when the text is the same with the same capitalization. If ordinary spaces or hidden characters differ, EXACT will also report a mismatch, so cleaning may be needed before comparison.
Text formatting and number conversion
TEXT converts a numeric value to text using a number format you choose. This is the right approach when joining a date or number with a label and you want a particular display, such as a month name or two decimal places. It changes the result into text, so it is usually not appropriate for a value that must remain numeric for later arithmetic.
With a real Excel date in B2, =TEXT(B2,"mmmm d, yyyy") returns a date displayed like “November 3, 2026” when B2 represents that date. With a numeric value in B2, a format such as =TEXT(B2,"0.00") returns text with two decimal places. When you concatenate a date without TEXT, Excel may show its serial number instead of the familiar date format.
VALUE converts text that Excel recognizes as a number into a numeric value, while NUMBERVALUE can specify the decimal and group separators explicitly. Conversion depends on the text and regional settings. Keep identifiers such as ZIP codes or account numbers as text when their digits are labels rather than quantities; converting them to numbers can remove leading zeros.
Choosing a function and avoiding common problems
Start by identifying the desired result: a joined label, a substring, a cleaned value, a position, or a true number. Then choose the simplest tool that expresses that goal. For example, use the ampersand for a few pieces, TEXTJOIN for a delimited list, FIND or SEARCH to locate a separator, and SUBSTITUTE to swap known text.
- If a formula gives an unexpected mismatch, check leading, trailing, repeated, or nonbreaking spaces.
- If a joined date or currency amount looks wrong, format it with TEXT before combining it with other text.
- If an extraction returns an error, check whether the delimiter is missing and whether the formula’s starting position is correct.
- If a value loses leading zeros, store or import it as text rather than converting it to a number.
- If case matters, use EXACT; the ordinary equals comparison does not distinguish uppercase from lowercase.
Excel supports up to 32,767 characters in a cell, but a result that is too long to display may be cut off visually even when the underlying value remains available. Also remember that a formula’s result may be text even when it looks numeric. If you need to sort, add, or average results, check the data type rather than relying on appearance alone.
In Microsoft 365, functions such as TEXTSPLIT can split text using delimiters, complementing the extraction and joining functions described above. TEXTSPLIT is available in Microsoft 365 and Excel 2024, not in every older Excel release. If a workbook must be shared with older versions, confirm that the recipient’s Excel supports any newer function you use.
Frequently Asked Questions
What is the Excel operator for joining text?
The ampersand (&) joins text values. Include spaces or punctuation in quoted text where they are needed. CONCAT and TEXTJOIN are alternatives, with TEXTJOIN especially useful for lists that share a delimiter.
What is the difference between FIND and SEARCH?
Both return the position of text inside another value. FIND is case-sensitive and treats wildcard characters literally; SEARCH ignores case and supports * and ? wildcards.
Why does my Excel text comparison say two cells are different?
They may contain extra spaces, nonprinting characters, nonbreaking spaces, or different underlying types. Clean the text and inspect the values; use EXACT only when case-sensitive matching is required.
How do I join text without adding a space for blank cells?
Use TEXTJOIN with its ignore-empty argument set to TRUE. Specify the delimiter you want, such as a comma and space, and provide the range containing the values.
How can I keep leading zeros in a code?
Store the code as text, for example by importing that column as text or formatting the cells as Text before entering values. A numeric conversion can discard leading zeros because they do not change the number’s value.
Related
- JavaScript split(): Syntax, Examples, and Common Mistakes
- Chemistry Balance Equation Worksheet: Practice and Answers
- Is the Balance of Payments Always Zero? Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel NPV Formula for Monthly Cash Flows, ExplainedFor equally spaced monthly cash flows in Excel, use the NPV function with a monthly discount rate, then…
- Excel Formula to Extract Unique Values from a ListIn Excel for Microsoft 365, enter =UNIQUE(A2:A20) to extract the distinct values from a list in cells A2:A20.…
- Average Value in Excel: Formula, Examples, and TipsTo calculate an average value in Excel, use the AVERAGE function: with the numbers 12, 15, 18, 21,…
- Excel Align Text Shortcut: Left, Center, Right & MoreIn Excel for Microsoft 365 on Windows, select the cell or range and press Alt, H, A, L…
- Excel Filter Shortcut: Apply, Remove, and Use AutoFilterIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to apply AutoFilter to a data range—or press the…
- Count Unique Values in Excel: Formulas and MethodsTo count distinct, nonblank values in Excel for Microsoft 365, use =ROWS(UNIQUE(FILTER(A2:A20,A2:A20<>""))). This assumes the values are in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.