There may be a workbook that contains text, but you are only looking to extract specific elements from it. For instance, you might just want to extract only the initial name of a person from a cell that includes the first and last name of the person. In this video tutorial, I’ll demonstrate how to modify text, and specifically I will demonstrate the best methods to eliminate the first four characters from Excel by using the following functions:
1.) Excel RIGHT Function
2.) Excel MID Function
3.) Excel REPLACE Function
Utilizing Excel’s RIGHT Function. Excel Right Function in Excel
In this case, I’d like to eliminate the first four characters of CV36 7BL, leaving the remaining 3 characters. Let’s suppose that the postcode is located in cell A2 of Excel. Excel spreadsheet. The formula for cell B2 is:
=RIGHT(A2,LEN(A2)-4)
How does this formula function? Let’s break it into pieces so that you can comprehend the way it operates.
Right Function
The RIGHT function removes the specified amount of text from the right-hand side of a text. For instance, =RIGHT(“bananas”,4) will produce “anas”
For more detail please visit>>>
LEN Function
The LEN function determines the length of a string. For instance, =LEN(“apples”) is likely to yield 6 since there are six characters within the string “apples”.
RIGHT(A2,LEN(A2)
This formula will yield eight. The first argument in the RIGHT function, you must define the text you want to use. In this case, it’s the cell number A2 i.e. the postcode. The second argument is that you must specify the number of characters you wish to extract. In this case, I’m employing the LEN function that returns the characters in CV36 7BL. CV36 7BL, which is 8. Space between CV36 and 7BL is considered an element. Formula =RIGHT(A2,LEN(A2) is translated to =RIGHT(A2,8) which results in CV36 7BL.
RIGHT(A2,LEN(A2)-4)
I’d like to eliminate the initial 4 characters, that’s why I add an (4) at the end in the formula. LEN(A2)-4 thus returns 4 (8-4=4).
If I reduce this even further, the right function is =RIGHT(A2,4) which returns CV36.
How do you remove the first nth character of Strings?
If you wish to eliminate the first nth character in the string, you can modify the -4 at the final part of the formula to any number of characters you wish 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 wish to eliminate the first two characters, change it to -2 , so it will be =RIGHT(A2,LEN(A2)-2) and so on.
Utilizing the Excel MID Function in Excel
Another method to eliminate the initial 4 characters of CV36 7BL’s postcode CV36 7BL is by using an Excel MID function. Let’s assume that the postcode is located in A2 cell and that the formula is in cell B2.
The formula now reads =MID(A2,5,LEN(A2))
How does this formula function? I will go over each part in the formula for MID.
The Function for MID
It is the Excel MID function extracts the middle of a text , based on the number of characters specified. For example, =MID(“bananas”,3,2) returns “na”. First argument: the string of text, or the cell reference you wish to take from. Second argument refers to the initial character you wish to extract. Third argument concerns the amount of characters you wish to extract.
=MID(A2,5
The formula says to start with the fifth character in CV36 7BL, the postcode. This means that it starts from the space since it represents the 5th character along to the left.
LEN(A2)
The LEN function returns the characters in CV36 7BL, the postal code. CV36 7BL, which is 8.
=MID(A2,5,LEN(A2))
If you simplify this formula, the MID function will be =MID(A2,5,8). It begins from the space and then extracts 8 characters along. Since there are only 3 characters following the space, it thus extracts 7BL.
How do you remove the first nth character of Strings?
If you wish to eliminate the first character in the nth digit, simply add a 1 to the MID function’s second argument. For instance, if I want to eliminate the first three characters, I would enter 4 into the MID function’s second argument, so it will be =MID(A2,4,LEN(A2)). If I’m looking to eliminate the first two characters, then simply enter 3 as the second argument, so it changes to =MID(A2,3,LEN(A2)).
Utilizing the Excel REPLACE Function in Excel
Following the idea of removing the initial 4 characters from the postcode CV36 7BL, I’ll demonstrate how to do this with Excel’s Excel REPLACE feature. Assume that you have the code in A2 while the formula is in cell B2.
Cell B2’s formula has changed to =REPLACE(A2,1,4 ,””)
I’ll now demonstrate how this formula is used.
Replace Function
The function REPLACE replaces the characters in the string with a different group of characters. The first argument to this function will be the string or cell you wish to replace characters in, i.e. the postcode of cell A2. The second argument concerns the location of the old text that you want to replace with characters. The third argument concerns the amount of characters you would like to replace the previous text with. The fourth argument concerns the new characters you’d like to replace the old text with.
REPLACE(A2,1,4,””)
First argument refers to the postal code found in cell A2. Second argument refers to the start code. I would like to start from the beginning, so I type 1. The next argument is 4. because I’m trying to replace the initial 4 characters with a new text. The final reason is the two quotation marks that refer to empty strings. I would like to replace the initial 4 characters with empty strings, so I’m left with just three characters.
How do you remove the first nth character of Strings?
To eliminate the first character, simply replace the third argument with the number of characters you wish to eliminate. For instance, if you would like to eliminate the first three characters, simply modify the third argument to 3, so that it is =REPLACE(A2,1,3 ,””).