Home

SiteMap

Excel formula to remove first four characters - 3 different formulas

← PrevNext →

Let us assume, I have some data in my Excel worksheet. From one particular column, I want to extract values after removing the first four characters. I am going to show you three different formulas using three Excel built-in functions to do this. The functions are "REPLACE()", "MID()" and "RIGHT()".

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,"")

remove first four characters in excel using replace function

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))

using excels mid() function to remove first four characters

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)

using excels right() function to remove first four characters



← PreviousNext →