SUBSTITUTE, TEXTBEFORE and TEXTAFTER practice exercises

Replace characters inside a text, and split a text at a separator without counting positions yourself.

Start the first exercise

How SUBSTITUTE, TEXTBEFORE and TEXTAFTER work

SUBSTITUTE swaps one piece of a text for another. It takes the text, the old text to find and the new text to put in its place. Add a number at the end to change only that occurrence. Leave it out and every match is replaced. SUBSTITUTE is case-sensitive. TEXTBEFORE and TEXTAFTER return the text before or after a separator. They only exist in Excel 365 and Excel 2024.

=SUBSTITUTE(A2,"-","") turns "555-0142" into "5550142" by replacing each dash with empty text. =TEXTBEFORE(A2," ") returns "Anna" from "Anna Berg", and =TEXTAFTER(A2,", ") returns "Anna" from "Berg, Anna". Join the two to flip a name: =TEXTAFTER(A2,", ")&" "&TEXTBEFORE(A2,", ") turns "Berg, Anna" into "Anna Berg".

TEXTBEFORE and TEXTAFTER return #N/A when the separator isn't in the text. Their last optional argument sets what to show instead, or you can wrap them in IFERROR. In older versions of Excel, use LEFT, MID and FIND to do the same job. The exercises below cover removing dashes, taking a first name, flipping a name and taking the text between parentheses.

  1. 1Remove the dashes with SUBSTITUTEIn B2, use SUBSTITUTE to remove every dash from the phone number in A2. Replace "-" with an empty text, "".
  2. 2Take the first name with TEXTBEFOREPremiumIn B2, use TEXTBEFORE to get the text in A2 before the first space.
  3. 3Flip "Last, First" with TEXTAFTERPremiumIn B2, turn the name in A2 into "Anna Berg". Join TEXTAFTER and TEXTBEFORE around ", " with a space between them.
  4. 4Take the text between parenthesesPremiumIn B2, take the department between the parentheses in A2. Use TEXTAFTER to take what comes after "(", and TEXTBEFORE around it to stop at ")".

Functions in this topic

Read the guide

Questions

How do I replace only the second occurrence of a character in Excel?

Give SUBSTITUTE a fourth argument with the occurrence you want. =SUBSTITUTE(A2,"-","/",2) changes only the second dash, so "12-05-2024" becomes "12-05/2024". Without the number, SUBSTITUTE replaces every match. The number counts matches of the old text from the left.

How do I split text in Excel if I don't have TEXTBEFORE?

Combine LEFT, MID and FIND. =LEFT(A2,FIND(" ",A2)-1) returns the first word, and =MID(A2,FIND(" ",A2)+1,LEN(A2)) returns the rest. You can also use Text to Columns on the Data tab for a one-time split, or press Ctrl+E for Flash Fill after typing an example.

How do I remove several different characters at once in Excel?

Nest one SUBSTITUTE inside another, one per character. =SUBSTITUTE(SUBSTITUTE(A2,"-","")," ","") removes both dashes and spaces. The inner one runs first, and the outer one works on its result. Add another layer for each extra character you want to remove.