1) Using REPLACE() function
Syntax
REPLACE(old_text, start_num, num_chars, new_text)
The function takes four parameters (or arguments). All the parameters are required.
* old_text: The text or the characters to remove or to be replaced by another text. You can also pass the reference of the cell whose text are to be replaced. It is required.
* start_num: The position of the character in the "old_text" that is to be replaced. This is required.
* num_chars: The total number of characters in the text (the old_text) that is to be replaced. In our example, its 4 characters. This is required.
* new_text: The new text of characters which will replace the "old_text". This is required.
The formula:
Let's say, I have some values in 2nd (B) column. I want to remove the first four characters and get the remaining characters. The formula to do this is simple.
=REPLACE(B2,1,4,"")
2) Using MID() and LEN() functions
Syntax
MID(text, start_num, num_chars)
The MID extracts values from the middle of a given text and a given number of characters. The MID function takes three parameters (or arguments). All are required.
* text: The text or characters or the reference of a cell.
* start_num: The location from where it will start extracting values in a text.
* num_chars: The total number of characters (from start_num) you want to extract.
The LEN() function returns the length of a text.
The Formula
=MID(B2,5,LEN(B2))
3) Using RIGHT() and LEN() functions
The RIGHT() function in Excel is used to extract a specified number of characters from the right side of a text.
Syntax
RIGHT(text, [num_chars])
The RIGHT function takes two parameters (or arguments). All are the required.
* text: The text or characters the reference of a cell.
* num_chars: The total number of characters it will extract from the right side of a given text.
The Formula
=RIGHT(B2,LEN(B2) -4)