Spreadsheets quickly become unreadable when columns contain poorly formatted strings, codes mixed with names, or data imported from files. Rather than spending hours retyping or cutting and pasting, Excel offers a toolbox of text functions that allow you to extract, clean, and recompose strings. Here, I review the essential functions — LEFT, RIGHT, MID, FIND/SEARCH — and show how to combine them with utilities like LEN or TRIM to transform raw data into clean, usable columns.
Somaire
In brief
🔍 LEFT, RIGHT, MID are used to extract substrings: LEFT and RIGHT for fixed lengths, MID for a segment starting from a given position.
📌 SEARCH (case-insensitive) and FIND (case-sensitive) locate a character or pattern; combined with MID, they allow extracting delimited elements within a cell.
🧼 Use TRIM and LEN to clean spaces and measure length before/after. This avoids subtle errors during extractions.
⚙️ In practice, text functions are often combined with calculation or lookup functions, for example to feed a pivot table, make conditional sums with SUMIF, or count with COUNTIF.
Why these functions are useful
One might think text functions are anecdotal, but they are central as soon as a data flow is not strictly tabular: compound names, barcodes merged with dates, phone numbers in varied formats. These functions avoid manual manipulations and allow automating cleaning. Moreover, they integrate very well with other function families (lookup, conditional, aggregates), which saves time on data preparation and reduces human errors.
Basic functions: syntax and examples
LEFT and RIGHT: extraction from the edges
LEFT(text, n) returns the first n characters from the left. Example: LEFT(“AB-1234”, 2) => “AB”. RIGHT(text, n) does the opposite: RIGHT(“AB-1234”, 4) => “1234”. These functions are ideal when the structure is stable (fixed codes, known suffixes).
MID: extract a segment from a position
MID(text, start_position, number_of_characters) allows extracting a medium-length string. For example, MID(“Client_2025_ID”, 8, 4) will return “2025”. The common difficulty is determining start_position: it is often calculated with SEARCH or FIND to find a separator (space, semicolon, underscore).
SEARCH vs FIND: case-insensitive or sensitive markers
SEARCH(pattern, text, [start_num]) finds the position of the pattern regardless of case; FIND is strict about case. If you are looking for a separator like “-” or “@”, use SEARCH; if case matters (alphanumeric codes distinct by uppercase/lowercase), prefer FIND.
Other utilities: NBCAR, SUPPRESPACE, MAJUSCULE
NBCAR(text) measures the length of the string; SUPPRESPACE(text) removes unnecessary spaces (multiple spaces or at the beginning/end). To standardize case, MAJUSCULE, MINUSCULE, and NOMPROPRE (capitalize the first letter) are useful before any comparison or search.
Concrete examples and common formulas
Here are several recurring cases and how to solve them with a simple combination of functions.
| Problem | Formula | Result |
|---|---|---|
| Extract the code after the dash “AB-1234” | =DROITE(A2;NBCAR(A2)-CHERCHE(“-“;A2)) | “1234” |
| Last name from “Dupont, Pierre” | =GAUCHE(A2;CHERCHE(“,”;A2)-1) | “Dupont” |
| Number between two dots “ID.4567.X” | =STXT(A2;CHERCHE(“.”;A2)+1;CHERCHE(“.”;A2;CHERCHE(“.”;A2)+1)-CHERCHE(“.”;A2)-1) | “4567” |
These formulas combine CHERCHE and index calculations: the key is to locate the positions of the delimiters then use STXT to isolate the desired portion. If delimiters may be missing, consider wrapping with SIERREUR to avoid #VALUE! errors.
Complex cases: variable fields, lists, and imports
Things get complicated when lengths are not fixed and multiple separators may coexist. A common example: an “address” column from an external file. Before extracting the city or postal code, clean the string with SUPPRESPACE then locate the separators. For large files, the operation becomes repeatable via an auxiliary column and reusable formulas.
If you work on bulk imported files, it is useful to master upstream processing of the file. Sometimes, a clean conversion during CSV import avoids lengthy manipulations afterwards.
Example: extract the part after the last space
To retrieve the last word (for example a suffix), use:
=STXT(A2;TROUVE(“§”;SUBSTITUE(A2;” “; “§”;NBCAR(A2)-NBCAR(SUBSTITUE(A2;” “;””))))+1;NBCAR(A2))
This formula temporarily replaces the last space with a unique character, calculates its position, then extracts what follows. It is a bit verbose but robust when the number of words varies.
Best practices and pitfalls to avoid
- Clean first: SUPPRESPACE and NBCAR help anticipate invisible spaces that distort CHERCHE/TROUVE.
- Prefer CHERCHE if case is unreliable; TROUVE when case matters.
- Consider SIERREUR around formulas if delimiters may be missing to preserve the sheet.
- Document your auxiliary columns: a cell with a complex formula soon becomes incomprehensible without comments.
- For repetitive transformations, writing a macro or using Power Query can be more efficient at scale.
Combining text and lookup/aggregates
Extracting an identifier to then find a value in another table is a common operation. After extraction, you can feed modern lookup functions. For example, extract a client code and pass it to RECHERCHEX to get the balance or corresponding contact. This combination avoids creating a manual ID column and makes your workbooks more dynamic.
Similarly, after isolating categories in a column, you can run conditional aggregates such as SUMIF or counts via COUNTIF to quickly produce reports.
Tip: Check Length and Consistency
Before any massive extraction, perform a quick quality check: create a column calculating LEN(original) and a column after extraction. An unexpected gap indicates an irregular format. You can even add an “OK” column that validates the expected format via a logical formula, which makes sorting and manual intervention on problematic cases easier.
Image prompt (generation): Realistic view of a computer screen displaying an Excel file with address columns and visible formulas (LEFT, MID, FIND), modern Office interface, subdued color, highlighting of cells with emphasis, sharp rendering for educational article illustration. –slug: extraction-texte-excel –alt: Example of text extraction in Excel
FAQ
When to use MID instead of LEFT/RIGHT?
MID is suitable when the portion to extract is in the middle and its position varies. LEFT/RIGHT are appropriate for fixed prefixes or suffixes. If the separator is variable, combine FIND and MID to dynamically locate and extract.
How to avoid #VALUE! errors with FIND?
Encapsulate the search in IFERROR or test the presence of the pattern with ISNUMBER(FIND(…)). This way, you handle rows where the separator is absent without interrupting your calculations.
Can all cleaning be automated without VBA?
Yes. For repetitive tasks, Power Query (Get & Transform) is often more powerful and maintainable than a mountain of formulas. However, for one-off corrections or simple extractions, text functions suffice and remain more accessible.
What is the performance on very large files?
Heavy formulas applied over tens of thousands of rows can slow down. In this case, prioritize Power Query, simplified formulas, or calculate extracts in steps to reduce overall recalculation.
{
“@context”: “https://schema.org”,
“@type”: “WebPage”,
“about”: {
“@type”: “Thing”,
“name”: “Excel Text Functions: Extraction and Cleaning (LEFT, RIGHT, MID, FIND)”
},
“keywords”: [“LEFT”, “MID”, “FIND”, “LEN”, “TRIM”]
}
