Remove text by variable position in Excel

Remove text by variable position in Excel 

Generic formula in Excel

Generic formula in Excel

=REPLACE(text,start,FIND(marker,text)+1,"")

Summary

In order to get rid of text from a cell, you can use a formula that uses the REPLACE function. You can also use the FIND function to help. In the example shown, the formula for C6 is as shown:

=REPLACE(B6,1,FIND(":",B6)+1,"")

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

Explanation

The REPLACE function will change text based on where it is. You can use REPLACE to get rid of text by giving it an empty string ().

In this case, we want to get rid of the labels that show up inside of the text. Some of the labels are longer than others, but they all have words like “Make,” “Model,” “Fuel economy,” and so forth in them, as well. There is a colon and a space after each name in the text. We can use the colon to figure out where the label ends.

To work our way from the inside out, we first use the FIND function to find the location of the colon in the text. Then we add 1 to account for the space that comes after it. Replacing a number of characters with a different number is done with the REPLACE function. The "num chars" argument tells REPLACE how many characters to replace.

The REPLACE function then changes the text that goes from 1 to "colon + 1" with an empty string, so that it looks like this: (""). As shown, the answer looks like this:

=REPLACE(B6,1,FIND(":",B6)+1,"")

=REPLACE(B6,1,6,"")

=2016

Enjoyed this article? Stay informed by joining our newsletter!

Comments

You must be logged in to post a comment.

Related Articles
About Author