Excel String Functions: What Each One Does

Formulas & functions · Updated November 22, 2026

Excel does not have one function called STRING; it provides a group of text functions for joining, splitting, searching, cleaning, converting, and formatting text. In Microsoft Excel for Microsoft 365 on Windows, the right function depends on the task: use TEXTJOIN to combine values, TEXTSPLIT to divide them, FIND or SEARCH to locate characters, and TRIM or CLEAN to tidy imported text.

What “string functions” means in Excel

In programming, a string is a sequence of characters. In Excel, the comparable category is usually called text functions. A cell can hold text such as a name, an identifier, or a date written as characters; a text function returns or changes text, though some functions convert between text and numbers.

There is no single function that handles every text operation. A common task—turning “Last, First” into two columns, for example—may require locating the comma and extracting the characters on either side. Newer Excel versions offer a direct splitting function, while older workbooks may use LEFT, MID, FIND, and LEN together.

Excel also distinguishes a number from text that looks like a number. The value 123 stored as a number can be added; “123” stored as text may need converting first. That distinction explains why choosing the right function matters for calculations, sorting, and reporting.

Functions for joining text

CONCAT combines text or cell values without adding a separator. TEXTJOIN combines items and lets you specify a delimiter—such as a comma or space—and whether to ignore empty cells. CONCATENATE is the older compatibility function; Microsoft recommends CONCAT for newer workbooks, while TEXTJOIN is more convenient when separators or blanks matter.

With names or labels in A2:A4, including a blank A3, the formula =TEXTJOIN(", ",TRUE,A2:A4) returns red, blue if A2 is red and A4 is blue. The delimiter is comma plus a space, and TRUE tells Excel to skip the empty cell. CONCAT would join the same range without inserting a delimiter.

Choose CONCAT when the pieces should run together, such as a prefix and an ID. Choose TEXTJOIN when the result needs consistent separators or when blank cells should not create doubled commas. These functions produce text; they do not perform arithmetic on the values they join.

Functions for extracting and splitting text

LEFT returns characters from the beginning of a text value, RIGHT returns characters from its end, and MID returns characters from a position you specify. LEN counts characters. These are useful for fixed-format codes, though they can be fragile when the number of characters varies.

With an identifier in A2, INV-2048, the formula =LEFT(A2,3) returns INV. For variable-length pieces, Excel for Microsoft 365 and Excel 2024 include TEXTBEFORE, TEXTAFTER, and TEXTSPLIT. With A2 containing INV-2048, =TEXTAFTER(A2,"-") returns 2048. If A2 contains North|South|East, =TEXTSPLIT(A2,"|") spills the three items into adjacent cells.

TEXTSPLIT can split by a column delimiter, a row delimiter, or both, which helps with pasted or exported data. The spill area must be empty or Excel will show a #SPILL! error. In older Excel versions without these newer functions, use Text to Columns for a one-time split, or combine FIND, LEFT, MID, and LEN in formulas.

Functions for finding and replacing text

FIND returns the position of one text string inside another and is case-sensitive. SEARCH also returns a position but ignores case and supports wildcard characters such as ? and *. Both report a #VALUE! error when the requested text is not found, so use IFERROR if a missing match is expected and should produce a different result.

SUBSTITUTE replaces matching text wherever it occurs; it can also target a particular occurrence. REPLACE instead replaces a specified number of characters starting at a position. For example, SUBSTITUTE is natural for changing every hyphen to a slash, while REPLACE is useful when a known section of a fixed-length code must be overwritten.

With a product code in A2 containing Item-42, =SUBSTITUTE(A2,"-","/") returns Item/42. Use exact text carefully: replacing a short sequence may change more places than intended. For case-sensitive matching, FIND is a better fit than SEARCH.

Functions for cleaning and changing letter case

TRIM removes leading and trailing ordinary spaces and reduces repeated spaces between words to a single space. CLEAN removes many nonprinting characters, which can arrive in copied or imported data. They address different problems: TRIM is about spaces, while CLEAN targets control characters. Neither removes every unusual Unicode character, and TRIM does not remove the nonbreaking space often found on web pages.

UPPER changes letters to uppercase, LOWER changes them to lowercase, and PROPER capitalizes the first letter of each word. These can standardize display, but PROPER is not a reliable name-correction tool: it may capitalize particles or abbreviations in ways a person would not choose. EXACT compares two text values and returns TRUE only when they match exactly, including letter case.

With unstandardized text in A2, =TRIM(A2) changes Ada Lovelace to Ada Lovelace. To remove control characters as well, a common approach is =TRIM(CLEAN(A2)). If the result still contains odd spacing, check for a nonbreaking space or another character that CLEAN does not remove; SUBSTITUTE can replace a known unwanted character.

Functions for converting and formatting values

VALUE converts text that Excel recognizes as a number into a numeric value. NUMBERVALUE gives more control over decimal and group separators, which is useful when imported data uses punctuation different from the computer’s regional settings. TEXT does the reverse kind of job: it displays a value using a format pattern but returns the result as text.

With the number 0.25 in A2, =TEXT(A2,"0%") returns the text 25%. That is convenient for combining a formatted number with a sentence, but the result is text rather than a percentage suitable for further arithmetic. If the goal is simply to show a percentage in a cell, applying percentage formatting from Home > Number is usually better.

With the text 1,234.50 in A2, =NUMBERVALUE(A2,".",",") returns the number 1234.5: the formula identifies the period as the decimal separator and comma as the group separator. Check the source data’s conventions before converting; otherwise, a value such as 1,234 may be interpreted differently than intended.

DOLLAR and FIXED also create formatted text from numbers. DOLLAR applies currency formatting, while FIXED formats a number with a chosen number of decimal places. Because both return text, they are primarily presentation tools, not replacements for number formatting when calculations must continue.

Character codes and less common text functions

CHAR returns a character for a numeric character code, and CODE returns a code for the first character in text. UNICHAR and UNICODE provide corresponding functions for Unicode characters, covering a much wider range of writing systems and symbols. These functions can help identify or construct characters, but the exact symbol displayed depends on the font and supported character set.

REPT repeats text a specified number of times, which can create simple repeated labels or visual markers. T returns its argument if it is text and an empty string otherwise; it has limited everyday use. ARRAYTOTEXT turns an array into a text representation in supported modern Excel versions. ASC and DBCS convert between full-width and half-width characters in languages where those forms are used, while PHONETIC relates to phonetic text in Japanese. BAHTTEXT converts a number to Thai text, and is specialized for Thai baht.

These less familiar functions are not substitutes for the core tools. For example, REPT can repeat a character, but it does not split a string; CODE can inspect a character code, but it does not clean a whole imported column. Use them when their specific behavior matches the data.

How to choose a function and avoid common errors

Start by deciding what the result should be: text, a number, or a displayed format. Then consider whether the source has a fixed structure. A fixed-width product code may suit LEFT or MID; a variable delimiter is often easier with TEXTBEFORE, TEXTAFTER, or TEXTSPLIT.

  • To combine values with a separator, use TEXTJOIN; to combine without one, use CONCAT.
  • To extract by position, use LEFT, RIGHT, or MID; to split around a delimiter, use TEXTSPLIT or the before/after functions.
  • To find text, use FIND for case-sensitive matching or SEARCH for case-insensitive matching.
  • To replace matching text, use SUBSTITUTE; to replace characters at set positions, use REPLACE.
  • To tidy common imported text, try TRIM and CLEAN, then investigate unusual spaces if problems remain.
  • To convert text to a usable number, use VALUE or NUMBERVALUE; to create a display string, use TEXT.

Two frequent problems are version availability and hidden characters. TEXTBEFORE, TEXTAFTER, and TEXTSPLIT require Microsoft 365 or Excel 2024; a workbook opened in an older version may show a function-name error. Also, visually identical spaces can be different characters, so a formula that removes ordinary spaces may leave a nonbreaking space untouched. When a lookup or comparison fails unexpectedly, inspect and normalize the source values before changing the lookup formula.

Frequently Asked Questions

What is the Excel function for a string?
Excel has no single STRING function. It has a set of text functions, including CONCAT, LEFT, MID, SEARCH, SUBSTITUTE, and TEXT, each designed for a different operation.

How do I combine text from several cells?
Use CONCAT to join values directly, or TEXTJOIN to add a delimiter and optionally skip blank cells. For example, TEXTJOIN can combine names with comma-space separators without leaving extra commas for empty cells.

How do I split text in Excel?
In Microsoft 365 or Excel 2024, TEXTSPLIT can divide a value at a delimiter and spill the results into cells. For a one-off split, use Data > Text to Columns; older Excel editions do not include TEXTSPLIT.

Why does Excel treat a number as text?
The cell may contain characters that look numeric rather than a stored number, often due to an import or a leading apostrophe. VALUE or NUMBERVALUE can convert recognizable text; check separators and any extra spaces if conversion fails.

What is the difference between FIND and SEARCH?
FIND is case-sensitive and does not use wildcards. SEARCH is case-insensitive and supports wildcard matching, so pick the function based on whether capitalization and wildcard characters should affect the match.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.