A spreadsheet cell can hold a number, a date (which is also a number) or text, a string of characters. You'll be handed text constantly, and it usually hides the fields you actually want to analyse. A product code encodes a category and a SKU. An invoice string encodes a state and a serial. A "name" column arrives with stray spaces and inconsistent capitalisation, so two identical customers look different to the computer. Text functions exist to parse that data (pull fields out), clean it (remove noise) and reshape it (recombine). Master them and you stop retyping data by hand, which is the biggest hidden time-sink in junior analyst work.
How do LEN, LEFT, RIGHT and MID work?
The measuring stick: LEN. LEN(text) returns the number of characters, spaces included. It's the humble check you run before every extraction, because position-based functions need to know where things are. A valid Indian GSTIN is 15 characters long, so =LEN("27AABCU9603R1ZM") returns 15. That makes =IF(LEN(A2)<>15,"CHECK","ok") a one-cell data-quality flag. Because LEN counts spaces, it doubles as a dirt detector. If a "clean" code shows a longer LEN than you expect, there's invisible whitespace in it.
Position-based extraction: LEFT, RIGHT, MID. These slice by character position.
LEFT(text, n), the firstncharacters.=LEFT("IN-MUM-4472-A", 2)returns"IN".RIGHT(text, n), the lastncharacters.=RIGHT("INV-00427", 5)returns"00427".MID(text, start, n),ncharacters beginning at positionstart, counting from 1.=MID("IN-MUM-4472-A", 4, 3)returns"MUM". Start at character 4 and take 3.
Position-based slicing is perfect when the layout is fixed (a GSTIN's first two characters are always the state code) and brittle when it is not (a city name that is sometimes three letters and sometimes four). For variable layouts you want delimiter-based extraction, covered in TEXTBEFORE, TEXTAFTER and TEXTSPLIT.
Advertisement
How do you clean text with TRIM?
Cleaning: TRIM, and the trap it can't fix. Imported text is full of stray spaces. TRIM(text) removes leading and trailing spaces, and it also collapses every run of internal spaces to a single space. Feed it the messy " Reliance Industries ". That's 25 characters: two leading spaces, three between the words and two trailing. =TRIM(...) returns "Reliance Industries", which is 19 characters, so six are gone. Trim before you compare or aggregate text. Otherwise "Reliance Industries" and "Reliance Industries" count as two different customers and split your totals.
You'll need this warning within your first week of real data. TRIM only removes the ordinary space, CHAR(32). Web and PDF exports often carry the non-breaking space CHAR(160), which looks identical on screen and gets through TRIM untouched. So substitute it out first: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")). CLEAN(text) does a similar job for non-printing control characters, CHAR(0) through CHAR(31). Those arrive from mainframe and system dumps. For a truly filthy cell, the professional cleaning idiom is =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))).
How do you change case and format numbers as text?
Case normalisation: UPPER, LOWER, PROPER. UPPER("mh") gives "MH". LOWER("Sales@Firm.COM") gives "sales@firm.com". PROPER("arjun mehta") gives "Arjun Mehta". Why bother? Excel's aggregation and matching are usually case-insensitive. Your eyes and downstream systems aren't. Normalising case makes a column consistent, typically with UPPER for codes and PROPER for names. PROPER is imperfect, though. It turns "McDonald" into "Mcdonald" and "iPhone" into "Iphone", so treat it as a first pass and check what it gives you.
The formatting inverse: TEXT. TEXT(value, format_code) takes a number or date and returns it as a string, formatted the way you specify. It's the mirror image of parsing text back into a number. You'll use it for two jobs. One is building labels people can read. The other is forcing a specific display inside a concatenation, where normal cell formatting doesn't apply. Verified examples: =TEXT(1234567, "#,##0") → "1,234,567"; =TEXT(1234.5, "$#,##0.00") → "$1,234.50"; =TEXT(0.0725, "0.00%") → "7.25%"; =TEXT(0.4267, "0.0%") → "42.7%". For dates, the format codes are calendar tokens: =TEXT(A2, "dd-mmm-yyyy") → "01-Jan-2025", =TEXT(A2, "mmm-yy") → "Jan-25". Hold on to one mental model. TEXT produces a string you can no longer do arithmetic on, so ="Total: "&TEXT(B2,"#,##0") is a label. When you need the number back, that's the job of VALUE, which comes up in the full module.
UI note. You rarely need the menus for these. Want to see what a format code does before you commit to it? Select a cell and press Ctrl+1 (Format Cells) ▸ Number ▸ Custom. The "Sample" preview shows the result live, and the code you build there is what TEXT's second argument accepts.