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 "". In the example shown, the solution looks like this:
To remove text from a cell by position, you can use the REPLACE function. In the example shown, the formula in C6 is: = REPLACE ( B6 , 1 , 24 , "" ) How this formula works The replace function lets you replace text based on its location...
To remove text from a cell based by matching content (not location), you can use the SUBSTITUTE function. In the example shown, the formula in C6 is: = SUBSTITUTE ( B6 , "-" , "" ) How this formula works The SUBSTITUTE function...
The Excel FIND function returns the position (as a number) of one text string inside another. When the text is not found, FIND returns a #VALUE error.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.