You may have a workbook which has text, however you only want to extract certain particular characters. For example, you may only need to extract the first name from a cell that has the first and the last name of the person. In this tutorial I will demonstrate how to manipulate text and in particular I will explain several great methods on how remove the initial 4 characters in Excel with the help of the following functions:
1.) Excel RIGHT Function
2) Excel MID Function
3.) Excel REPLACE Function
Making use of Excel’s RIGHT Function. Excel RIGHT Function
In this example I want to remove the first 4 characters from the postcode CV36 7BL and leave the last three characters. Let’s suppose that the postcode is in cell A2 in Excel. Excel spreadsheet. The formula for cell B2 is:
So how does this formula perform? Let’s break it down to help you understand how it is working.
The RIGHT function is a way to extract an arbitrary number of characters from the right-hand side of the text. For example, =RIGHT(“bananas”,4) will result in “anas”
The LEN function extracts the length of a string. For instance =LEN(“apples”) would produce 6 because there are six characters in the string “apples”.
For more detail please visit>>>
This section of the formula will result in the number 8. The first argument in the RIGHT function you have to select the text to use. In this case it’s cell A2 i.e. the postcode. To make the second argument, you will need to provide the characters you would like to extract from. For this , I am applying the LEN function that returns the number of characters of CV36 7BL. CV36 7BL which is 8. A space in between CV36 and 7BL counts as an element. The formula =RIGHT(A2,LEN(A2) translates to =RIGHT(A2,8) which results in CV36 7BL.
I’d like to eliminate the 4 initial characters it is necessary to add an (4) at the end of this formula. LEN(A2)-4 therefore returns 4 (8-4=4).
If I break it down further the right function is =RIGHT(A2,4) that returns the CV36.
How can you remove the First Character in a String?
If you want to remove the first characters of a string you just change the -4 at the ending of the formula to whatever number of characters you would like to eliminate. For example, if you want to remove the first 3 characters of a string then simply change the -4 to -3. so the formula becomes =RIGHT(A2,LEN(A2)-3). If you’d like to delete the first two characters, change it to -2 , so it becomes =RIGHT(A2,LEN(A2)-2) and so on.
Using the Excel MID Function
Another way to remove the first four characters from CV36 7BL’s postcode CV36 7BL is by using an Excel MID function. The postcode will be at cell A2 and your formula is in cell B2.
The formula now reads =MID(A2,5,LEN(A2))
How does this formula function? I will explain each section that makes up the MID formula.
The MID Function
It is the Excel MID function extracts the middle of a text based on the specified number of characters. For example, =MID(“bananas”,3,2) returns “na”. The first argument is the text string, or the reference to a cell that you would like to extract from. Another argument is the primary character you’d like to extract. Another argument is about the amount of characters you wish to extract.
The formula suggests starting with the fifth character in the CV36/7BL postcode. This means it starts with the space because the space is the fifth character to the left.
The LEN function is returning the number of characters of CV36 7BL, the postal code. CV36 7BL which is 8.
If you simplify this formula, the MID function will be =MID(A2,5,8). It starts with the space and adds 8 characters along. Since there are only 3 characters after the space , it therefore extracts 7BL.
How do you remove the First nth Character from the String?
If you’d like to delete the first character in the nth digit, just add a 1 in the MID functions second argument. For instance, if I want to eliminate the first three characters, I will enter 4 in the MID functions second argument so it’s =MID(A2,4,LEN(A2)). If I’m looking to eliminate the first two characters, then just enter 3 for the second argument, so it becomes =MID(A2,3,LEN(A2)).
Using the Excel REPLACE Function
Following on from the topic of removing 4 characters from CV36 7BL I will now demonstrate how to do this using an Excel REPLACE formula. Again I assume this postcode can be found in cells A2 as well as the formula is in cell B2.
Cell B2’s formula now reads =REPLACE(A2,1,4 ,””)
Let me now show you how this formula functions.
The function REPLACE replaces a set of characters in the string by a different group of characters. The first argument to the replace function is the string or the cell you are replacing characters with, i.e. the postcode found in cell A2. The second argument is the position of the previous text to be replaced with the new. The third argument is the amount of characters you want to replace the old text with. The fourth argument is the new characters you want to replace the text with.
The first argument is the postcode that is in cell A2. Second argument refers to the start code. I want to start from the beginning so I enter 1. The 3rd argument is 4 because I’m hoping to replace the 4 initial characters with new text. The last argument is two quotation marks which are empty strings. I’d like to replace the initial 4 characters with empty strings. So I am left with the final three characters.
How can you remove the first character in String?
To remove the first character, simply replace the third argument with the number of characters you would like to remove. For example, if you want to remove the first 3 characters , you can change the third argument to 3 and it will become =REPLACE(A2,1,3 ,””).