which removes all text up to and including the colon (:) and following space.
How this formula works
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:
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 , "" ) which replaces the first 24 characters in the text with an empty string (""). How this...
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...
To remove the last word from a text string, you can use a formula based on the MID function , with help from SUBSTITUTE , LEN , and FIND . In the example shown, the formula in cell B5 is: = MID ( B5 , 1 , FIND ( "~" , SUBSTITUTE ( B5 ,...
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.