To remove text from a cell, when the text is at a variable position, you can use a formula based on the REPLACE function, with help from the FIND function. In the example shown, the formula in C6 is:


which removes all text up to and including the colon (:) and following space.

Generic formula



The REPLACE function will replace text by position. You can use REPLACE to remove text by providing an empty string ("") for the "new_text" argument.

In this case, we want to remove the labels that appear inside text. The labels vary in length, and include words like "Make", "Model", "Fuel economy", and so on. Each label is followed by a colon and a space. We can use the colon as a "marker" to figure out where the label ends.

Working from the inside out, we use the FIND function to get the position of the colon in the text, then add 1 to take into account the space that follows the colon. The result (a number) is plugged into the REPLACE function for the "num_chars" argument, which represents the number of characters to replace.

The REPLACE function then replaces the text from 1 to "colon + 1" with an empty string (""). In the example shown, the solution looks like this:

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.