You may have a workbook containing text but only want to extract certain characters from it. For example, you may only want to remove the first name from a cell containing the person’s name and surname. In this tutorial, I will show you how to edit text and in particular, I will show you good tips on how to remove the first 4 characters in Excel using the following functions:
1) Excel FOOD function
2) Excel MID function
3) Excel REPLACE function
Using Excel RIGHT Function
In this example, I want to remove the first 4 characters from the postcode CV36 7BL and leave the last 3 characters. Let us assume that the postal code is in cell A2 in the Excel spreadsheet. The formula in cell B2 will be:
= RIGHT (A2, LEN (A2) -4)
So how does this formula work? Let’s postpone this to understand how it works.
OK job
The RIGHT function removes a certain number of characters from the right side of a text. Example = OK (“bananas”, 4) will lead to “anas”
LEN function
The LEN function specifies the length of a given character unit. Example = LEN (“apples”) will lead to 6 as there are 6 characters in the string “apples”.
RIGHT (A2, LEN (A2)
This section of the formula will return 8. In the first conflict of the RIGHT function you must specify which text to use. In this example it is cell A2 which is the postal code. In the second argument, you must specify the number of characters you want to extract. In this dispute I use the LEN function which returns the 8-digit postal code CV36 7BL. The space between CV36 and 7BL is calculated as a character. The formula = RIGHT (A2, LEN (A2) translates to = RIGHT (A2,8) which returns CV36 7BL.
RIGHT (A2, LEN (A2) -4)
I want to subtract the first 4 letters so I put -4 at the end of the formula. LEN (A2) -4 therefore returns 4 (8-4 = 4).
If I simplify this further the RIGHT function = RIGHT (A2,4) and return CV36.
How do you remove the First Character of the Character Unit?
If you want to delete the first nth characters in a string simply turn -4 at the end of the formula into any number of characters you want to delete. For example, if you want to delete the first 3 characters of a character unit just change the -4 to -3. so the formula becomes = RIGHT (A2, LEN (A2) -3). If you want to delete the first 2 characters then change it to 2 so that it is = RIGHT (A2, LEN (A2) -2) and so on.
Using Excel MID function
Another way to extract the first 4 characters from the CV36 7BL postcode is to use Excel MID function. Also consider that the postal code is in cell A2 and the formula is in cell B2.
The formula now says = MID (A2,5, LEN (A2))
So how does this formula work? I will explain each section of the MID formula.
MID function
The Excel MID function extracts between text based on a specified number of characters. For example, = MID (“bananas”, 3,2) returns “and”. The first argument is the text string or cell reference you want to extract from. The second argument is the first letter you want to take out. The third argument is the number of characters you want to extract.
= MID (A2,5
This section of the formula states that it starts with the fifth letter of the postal code CV36 7BL. This means it will start in space as space is the fifth character from the left.
LEN (A2)
The LEN function returns the 8-digit postal code CV36 7BL.
= MID (A2,5, LEN (A2))
When you simplify this formula the MID function is = MID (A2,5,8). It starts in space and releases 8 characters in a row. Because there are only 3 characters behind the space so it emits 7BL.
How do you remove the First Character of the Character Unit?
If you want to delete the first nth character just add 1 to the second argument of MID functions. For example if I want to remove the first 3 characters and add 4 to the MID function the second argument to be = MID (A2,4, LEN (A2)). If I want to delete the first 2 characters just add 3 to the second argument to be = MID (A2,3, LEN (A2)).
Using Excel REPLACE function
Continuing with the theme of extracting the first 4 characters from the post code CV36 7BL Now I will show you how to do this using the Excel REPLACE function. And I think the postal code is in cell A2 and the formula is in cell B2.
The formula in cell B2 now says = REPLACE (A2,1,4, “”)
Now I will show you how this formula works.
REPLACE function
The REPLACE function replaces a set of characters in a character unit and another set of characters. The first argument for the switch function is the character unit or cell you want to replace the characters with, i.e. the postal code in cell A2. The second argument is the location of the old text to start replacing characters. The third argument is the number of characters you want to replace with old text. The fourth argument is the new characters with which you want to replace the old text.
BUSELELA (A2,1,4, “”)
Opposition to start postal code in cell A2. The second argument is number one. I want to start from scratch to enter 1. The third argument is 4 as I want to replace the four letters with a new letter. The last argument is two quotation marks that mean empty strings. I want to replace 4 characters with empty characters so that I have the last 3 characters left.
How do you remove the First Character of the Character Unit?
To delete the first nth character just add a third argument instead of the number of characters you want to delete. For example, if you want to delete the first 3 characters just convert the third argument into 3 to REPLACE (A2,1,3, “).
I hope you enjoyed this tutorial on how to delete the first 4 characters in Excel. If you have questions or know of other ways to manipulate text in Excel please leave a comment on my website:
