A lot of data arrives in a spreadsheet in a form that is not the one you need: first and last name in the same cell, product codes with prefixes to separate, email addresses from which to derive the domain, lists with stray spaces and random capitals. Text functions let you fix all this with repeatable formulas instead of by hand, row by row. The names here are the English ones; the Italian Excel equivalents are in brackets.
Extracting parts of a text
The three basic functions take a certain number of characters from a position:
LEFT(text, n)(SINISTRA): the first n characters;RIGHT(text, n)(DESTRA): the last n characters;MID(text, start, n)(STRINGA.ESTRAI): n characters starting from the given position.
With a fixed-structure code such as "MI-2026-00457", where the first two letters indicate the branch, =LEFT(A2, 2) returns "MI", =MID(A2, 4, 4) returns "2026" and =RIGHT(A2, 5) returns "00457".
Watch out: the result is always text. "2026" extracted this way is not a number and will not be summed or compared as one. To convert it use VALUE() or multiply by 1: =MID(A2, 4, 4)*1.
Finding the position of a character
When the parts do not have a fixed length you need to know where the separator is:
FIND(what, text)(TROVA) returns the position and is case sensitive;SEARCH(what, text)(RICERCA) does the same without case sensitivity and accepts wildcards.
To derive the domain from an email address in A2:
=MID(A2, FIND("@", A2)+1, LEN(A2))
FIND locates the at sign, you start from the following character and ask for enough characters to cover the rest: the LEN of the whole text is always enough.
To split first and last name written as "Mario Rossi", with a space as the separator:
First name: =LEFT(A2, FIND(" ", A2)-1)
Last name: =MID(A2, FIND(" ", A2)+1, LEN(A2))
It works as long as there is only one space. With "Maria Grazia De Luca" no formula can know where the first name ends and the surname begins: in such cases the right solution is to ask for the data already separated at the source.
The newer functions
Recent versions of Excel have TEXTBEFORE and TEXTAFTER (TESTO.PRIMA and TESTO.DOPO), which directly do what the combination of functions above achieves: =TEXTAFTER(A2, "@") returns the domain. Google Sheets offers SPLIT, which divides text into several cells by a separator. If the file has to work everywhere, the classic functions remain the safest choice.
Cleaning text
Data copied from other applications brings invisible dirt with it that breaks lookups and comparisons.
| Function | What it does |
|---|---|
TRIM (ANNULLA.SPAZI) |
removes leading and trailing spaces and reduces multiple spaces to one |
CLEAN (LIBERA) |
removes non-printable control characters |
UPPER / LOWER (MAIUSC / MINUSC) |
all capitals or all lower case |
PROPER (MAIUSC.INIZ) |
capital initial for every word |
SUBSTITUTE (SOSTITUISCI) |
replaces one text with another |
A frequent case: TRIM does not remove the non-breaking space (character 160) that comes from web pages. You get rid of it like this:
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))
PROPER should be used with judgement: it turns "MCDONALD" into "Mcdonald" and "D'ANGELO" into "D'angelo". For lists of names it is worth checking the result.
SUBSTITUTE is also very useful for normalising: removing hyphens from a phone number, swapping a full stop for a comma, stripping a prefix. With an optional fourth argument it replaces only the specified occurrence.
Joining text
The & operator joins text and values: =B2&" "&C2 returns "Mario Rossi". For numbers and dates it is better to use the TEXT function, otherwise the serial number will appear:
="Due: "&TEXT(D2, "dd/mm/yyyy")
Keep in mind that format codes depend on the language: in an Italian Excel the equivalent format is "gg/mm/aaaa".
To join many values with a separator, TEXTJOIN (TESTO.UNISCI) is more practical:
=TEXTJOIN(", ", TRUE, A2:A20)
The second argument, TRUE, ignores empty cells. It is the quickest way to get a comma-separated list of addresses to paste into an email.
Formula or tool?
For one-off jobs a tool is sometimes faster: in Excel, Data, Text to Columns splits a column by a separator; Flash Fill (Ctrl+E) recognises a pattern from the examples you type and applies it to the rest of the column. LibreOffice Calc has a Text to Columns command in the Data menu, and Google Sheets offers splitting text into columns in the same menu.
Formulas, however, have a decisive advantage when the data is updated periodically: you paste the new extract and the result recalculates. In my work, if a clean-up will need repeating even once more, I write it as a formula.
In short
- LEFT, RIGHT and MID extract parts of text; the result is always text.
- FIND and SEARCH locate separators when the parts vary in length.
- TRIM, CLEAN and SUBSTITUTE remove invisible dirt.
- To join values use
&with TEXT for dates and numbers, or TEXTJOIN. - For repeated work prefer formulas over one-off tools.
