How to Remove First 4 Characters in Excel
How to Remove First 4 Characters in Excel
You may have a workbook which contains text but you only want to extract specific elements from it. For instance, you might just want to extract only the initial name of a person from the cell that includes the first and final name of the person. In this guide, I'll explain how you can alter text and, in particular, I will show you some great techniques on how to eliminate the first four characters in Excel using the following functions: 1) Excel RIGHT Function https://www.g-barber.ch/ 2) Excel MID Function 3) Excel REPLACE Function Using the Excel RIGHT Function https://www.lionsoul.ch/ In this particular example, I'm trying remove the 4 first characters of CV36 7BL. Leave the remaining 3 characters. Let's assume that the postcode is in the cell A2 in the Excel spreadsheet. The formula in cell B2 will be: =RIGHT(A2,LEN(A2)-4) How does this formula work? Let's break this down to help you understand how it is working. RIGHT Function The RIGHT function is able to extract the required quantity of words from the right-hand side of a text. For example =RIGHT("bananas",4) will produce "anas" https://www.evodrop.com/ LEN Function The LEN function extracts the length of the string. For example, =LEN("apples") is likely to result in 6 as there are six characters in the string "apples". RIGHT(A2,LEN(A2) This section of the formula will give you 8. The first argument in the RIGHT function, you need to define the text you want to use. In this example , it's cells A2 i.e. the postcode. In the second argument, you will need to provide the number of characters you'd like to extract. In this case, I am employing the LEN function which returns the number of characters of CV36 7BL. CV36 7BL, which is 8. In between CV36 and 7BL is considered an element. This 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 delete the first four characters and therefore I include an extra -4 at the bottom of this formula. LEN(A2)-4 therefore returns 4 (8-4=4). If I simplify this further the right function is =RIGHT(A2,4) and returns CV36. How do you Remove the First nth Character of a String? If you want to remove the first nth character from a string you just change 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'd like to get rid of the first two characters, change it to -2 and it is =RIGHT(A2,LEN(A2)-2) and so on. Using the Excel MID Function Another method of removing the first four characters from the postal code CV36 7BL is to use 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)) What is the formula that function? I will explain each section of the formula. The MID Function The Excel MID function extracts the middle of a sentence based on the number of characters specified. For example, =MID("bananas",3,2) returns "na". One argument will be the text or the reference to a cell that you would like to take from. Another argument is the first character you wish to extract. Another argument concerns the number of characters you want to extract. =MID(A2,5 The formula is saying start at the fifth letter of CV36 7BL's postcode. This means that it starts with the space since it is the 5th character along from the left. LEN(A2) The LEN function returns the number of characters of CV36 7BL, the postal code. CV36 7BL, which equals 8. =MID(A2,5,LEN(A2)) If you simplify this formula, the MID function is =MID(A2,5,8). It begins from the space and adds 8 characters. Since there are only 3 characters following the space, it extracts 7BL. How do you Remove the First nth Character of a String? If you wish to eliminate the first character in the nth digit, just put a 1 in the MID function's second argument. If, for instance, I want to erase the first 3 characters then I enter 4 in the MID functions second argument . So it becomes =MID(A2,4,LEN(A2)). If I'm looking to eliminate the first 2 characters then you can enter 3 in the second argument. It will be =MID(A2,3,LEN(A2)). Using the Excel REPLACE Function Following the idea of removing the initial 4 characters from the postcode CV36 7BL I will now demonstrate how to do this by using the Excel REPLACE tool. Again I assume your postcode will be in the cell A2 and that the formula is in cell B2. In cell B2, the formula is =REPLACE(A2,1,4 ,"") I'll show you how this formula works. The REPLACE Function The function REPLACE replaces a set of characters in the string by a different group of characters. The first argument to this function will be the string or the cell you want to replace characters in, i.e. the postcode of cell A2. The second argument is where the old text to begin replacing characters. The third argument concerns the number of characters that you are looking to replace the old text with. The fourth argument concerns the new characters you want to replace the text with. REPLACE(A2,1,4,"") The first argument is the postcode found in cell A2. Second argument refers to the start number. I want to start from the beginning, so I type 1. The third argument is 4 as I want to replace the first 4 characters with new ones. The last argument is two quotation marks, which are empty strings. I'd like to replace these first four characters by empty strings. So I am left with the final 3 characters. How do you Remove the First nth Character of a String? To remove the first character just replace the third argument with the number of characters you want to get rid of. For instance, if you want to delete the first three characters, simply change the third argument to 3 so it becomes =REPLACE(A2,1,3 ,""). I hope you enjoyed this instructional video on how to get rid of the first four characters from Excel. If you have any questions or if you know of any other ways to alter text in Excel and you would like to share them with me, please post a comment on my blog:

Leave a Reply

Your email address will not be published. Required fields are marked *