Funcognito — Explore and learn anything, you got this!

2.5 Text functions and cleaning up strings

Checked against Microsoft's Excel for Windows keyboard references, August 2026

తెలుగులో చదవండి

What you'll be able to do

Split, join and repair messy text with formulas. Names go into separate columns, codes come out of longer text, and stray spaces are removed — instead of retyping a thousand rows by hand.

Before you start

Download the messy data workbook. Its dirt is deliberate: mixed capital letters, hidden spaces, and fields stuck together.

The work

  1. Same either way: start with the measuring tools. =LEN(A2) counts the characters, and it is the instrument that proves two cells that look identical are not. =FIND("@", A2) returns the position of a character, and it treats capitals as different letters. SEARCH does the same thing and ignores capitals. You rarely show these in a finished sheet. You diagnose with them.
  2. Same either way: the classic cutting tools take pieces by position. =LEFT(A2, 3) takes the first three characters. =RIGHT(A2, 4) takes the last four. =MID(A2, 5, 2) takes two characters starting at the fifth. Put together with FIND, they cut by structure: =LEFT(A2, FIND("@", A2)-1) is everything before the @ sign, which is the user name out of an email address.
  3. Same either way: the newer functions say the same thing in plain words. =TEXTBEFORE(A2, "@") and =TEXTAFTER(A2, "@") read as written. =TEXTSPLIT(A2, ",") breaks a comma-glued cell into columns in one move, spilling across as many cells as it needs. Use these where your Excel has them, and read the older LEFT and FIND spellings fluently.
  4. Same either way: joining is TEXTJOIN. =TEXTJOIN(", ", TRUE, A2:C2) glues a range together with a separator and skips the empty cells, which is how an address is assembled from parts. For small joins, the ampersand does it in place: =A2&" "&B2.
  5. Same either way: now the repair kit. =TRIM(A2) removes spaces at the start, at the end, and doubled spaces in the middle. It is the most-used repair in Excel. =CLEAN(A2) removes characters that do not print, which arrive pasted in from other systems. =PROPER(A2), =UPPER(A2) and =LOWER(A2) fix capital letters. The invisible-space problem from the lookup topic is TRIM's home ground: a key that LEN says is one character too long.
  6. Same either way: two functions swap text, and they answer different questions. =SUBSTITUTE(A2, "-", "/") replaces a given text everywhere it appears, or only the one you number in the optional last part. REPLACE overwrites by position instead: characters 5 to 7, whatever happens to be there. Know which question you are asking: this text, or this place?
  7. Same either way: formulas never change the original. They work out repaired copies beside it. When the repair is final, select the formula column and copy it with CtrlC. Then paste the values only over it: open the Paste Special dialog with CtrlAltV, choose Values, and press Enter. After that the original helper columns can go.

Try it

Work through the messy workbook. Use TRIM and PROPER on the name column, and prove with LEN that the dirt is gone. Split the email column at the @ sign. Assemble a display name with TEXTJOIN. Finish by pasting values over the repaired columns.

Check yourself

The longer one holds an invisible character, usually a space at the end. =TRIM() repairs it, and =CLEAN() deals with characters that do not print. LEN afterwards proves it worked.

Old: =LEFT(A2, FIND("@", A2)-1). New: =TEXTBEFORE(A2, "@"). Same result, said plainly.

SUBSTITUTE, because it targets a text wherever it appears. REPLACE targets a position, whatever sits in it.

The repair is a formula reading the original. Delete the original and the formula breaks. Pasting values freezes the repaired text so that it stands on its own.

Go deeper

We haven't checked most of these for screen reader use yet.

Back to Text functions and cleaning up strings: work through the checklist