Back to Excel course main page

String functions and advanced formulas

Excel has a set of powerful text manipulation formulas. In this lecture you will learn to apply SUBSTITUTE(), MID(), FIND() and LEN() functions in real situation. The examples require using nesting formulas which means that there will be a formula inside another formula. The order of evaluation is similar to normal calculus: inner formulas first. The Evaluate formula tool is very convenient to examine the process.

Exercises

  1. Use the table in the video tutorial but insert two new columns to the table. Name them “First space” and “Length”. Instead of nesting the LEN() and FIND() formulas inside the MID() function, calculate the text length and the position of the first space to these helper columns. Then reference to the new columns from the MID() function.

  2. How could you use RIGHT() instead of MID()? Try it in the table.

See the answer

  1. The MID() formula would be like this:
=MID([@Name];[@[First space]]+1;[@Length]-[@[First space]])

Note that the column name is wrapped in extra brackets if the name contains a space.

  1. The RIGHT() formula returns specified number of characters from the end of the string. In this case the RIGHT() would be actually a simpler choice.
=RIGHT([@Name];[@Length]-[@[First space]])

Lecture materials

Lecture 8 - Text formulas and advanced functions in Excel

Video tutorial