Explanation
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:
=REPLACE(B6,1,FIND(":",B6)+1,"")
=REPLACE(B6,1,6,"")
=2016
Explanation
The SUBSTITUTE function can find and replace text in a cell, wherever it occurs. In this case, we are using SUBSTITUTE to find a character with code number 202, and replace it with an empty string (""), which effectively removes the character completely.
How can you figure out which character(s) need to be removed, when they are invisible? To get the unique code number for the first character in a cell, you can use a formula based on the CODE and LEFT functions:
=CODE(LEFT(B4))
Here, the LEFT function, without the optional second argument, returns the first character on the left. This goes into the CODE function, which reports the characters code value, which is 202 in the example shown.
For more general cleaning, see the TRIM function and the CLEAN function .
All in one formula
In this case, since we are stripping leading characters, we could combine both formulas in one, like so:
=SUBSTITUTE(B4,CHAR(CODE(LEFT(B4))),"")
Here, instead of providing character 202 explicitly to SUBSTITUTE, we are using CODE and CHAR to provide a code dynamically, using the first character in the cell.
