Text Functions

Open any spreadsheet and the cells hold two kinds of thing. Some are numbers you can add up. The rest are words — names, cities, PAN codes, email addresses. In Excel these words are called text (or a text string), and just like numbers, text can be measured, cut, cleaned and glued back together with formulas.

Here is the picture to keep in your head. Text functions are a little craft kit for words.

• Scissors — LEFT, MID and RIGHT snip characters off the start, middle or end.
• A ruler — LEN measures how many characters there are, and FIND and SEARCH tell you the position of a letter.
• An eraser — TRIM rubs out stray spaces, and SUBSTITUTE rubs out any characters you name and swaps in new ones.
• Glue — the & sign, CONCAT and TEXTJOIN stick pieces of text together.
• A label-maker — UPPER, LOWER and PROPER reprint your words in CAPITALS, small letters, or Nicely Capitalised.

Why bother? Because real data arrives messy. One person types " ravi kumar " with stray spaces, another shouts RAVI in capitals, a third pastes a PAN as abcde1234f. Text functions let you fix a whole column in one go instead of retyping a thousand rows by hand. That is the everyday magic that turns an afternoon of typing into two seconds.

Everything below rests on that one idea: a cell of text is just a row of characters at positions 1, 2, 3… and every tool here either measures those positions, cuts at them, cleans them, or glues new ones on.

Two maps of the same toolkit: first the whole kit at a glance, then a decision path for picking the right tool.

Every text function works on one idea: a cell is a line of characters sitting at positions 1, 2, 3 and onwards. Point at the right positions and you can cut, measure, clean or glue. Here is that ruler for the name RAVI KUMAR.

Now take the toolkit one drawer at a time.

Cut characters — LEFT, RIGHT and MID. These are your scissors.

• LEFT(text, n) keeps the first n characters. LEFT("RAVI KUMAR",4) gives RAVI.
• RIGHT(text, n) keeps the last n. RIGHT("560001",3) gives 001, handy for the tail of a pincode.
• MID(text, start, n) begins at position start and takes n characters. MID("RAVI KUMAR",6,5) gives KUMAR.

Measure — LEN. LEN(text) counts every character, spaces included. LEN("ABCDE1234F") is 10, which is exactly how many characters a PAN should have — a quick validity check.

Clean and recase — TRIM, UPPER, LOWER, PROPER.

TRIM(text) is the eraser you will reach for most. It strips spaces from the start and end and squeezes any double spaces inside down to one, so " ravi kumar " becomes ravi kumar. It is the number-one real-world fixer, because copied-and-pasted data is riddled with invisible spaces.

The three case tools relabel your words. UPPER shouts in CAPITALS (great for PAN codes), LOWER whispers in small letters (great for email addresses), and PROPER is the label-maker that Capitalises The First Letter Of Every Word — perfect for names and cities.

Glue pieces together — &, CONCAT, CONCATENATE and TEXTJOIN.

The simplest glue is the ampersand. =A2 & " " & B2 sticks the first name, a space, and the last name into one cell. CONCAT does the same as a function: =CONCAT(A2," ",B2). CONCATENATE is its older twin — identical result, longer name, kept for backwards compatibility. Whichever you pick, you must add the space yourself; Excel never guesses it.

When you need to join a whole list — every city in a column, say — reach for TEXTJOIN(delimiter, ignore_empty, range). The first argument is the separator to drop between items, the second is TRUE to skip blank cells, and the third is the range. =TEXTJOIN(", ",TRUE,A2:A4) turns three cells into Aarav, Diya, Kabir with the commas placed for you.

Find a position, or swap characters — FIND, SEARCH and SUBSTITUTE.

FIND(what, inside) tells you the position of a character. FIND(" ","RAVI KUMAR") returns 5 because the space is the fifth character. SEARCH does the same job with two differences: it ignores case and it accepts wildcards. So FIND("k","Kumar") fails, while SEARCH("k","Kumar") happily returns 1.

SUBSTITUTE(text, old, new) is find-and-replace inside a single cell. =SUBSTITUTE("A-B-C","-","") deletes every hyphen to give ABC, and =SUBSTITUTE("Mr Rao","Mr","Shri") swaps a title. An optional fourth argument lets you change only the first, second or nth occurrence instead of all of them.

Combine them — the real power. The magic happens when one function feeds another. To pull the first name out of A2, first FIND the space, then LEFT up to just before it: =LEFT(A2,FIND(" ",A2)-1). To build a tidy email from a first and last name: =LOWER(CONCAT(A2,".",B2,"@pdcampus.in")) gives ravi.kumar@pdcampus.in. Read a nested formula from the inside out — the inner function runs first, and its answer becomes an argument for the outer one.

Text looks simple, so it trips up almost everyone. Here are the mistakes to expect and the fixes the pros reach for.

Eight real cells to fix. Type a formula, press Run, and watch the cell turn green when your text comes out exactly right.