How to remove the first four characters from an Excel spreadsheet

You can have a spreadsheet with text in it that you just want to extract certain characters from. For example, you might just want to extract a person's first name from a cell that contains both their first and last names. In this article, I will teach you how to change text and in particular how to delete the first four characters of Excel using the following features:

1) Excel RIGHT function

2) Excel MID function

3) Excel REPLACE function

Using the Excel RIGHT function

In this example, I want to remove the first four characters from the zip code CV36 7BL while keeping the last three characters. Assume that the zip code is in cell A2 in the Excel spreadsheet. Cell B2 will contain the following formula:

= RIGHT (A2, LEN (A2) -4)

So, exactly how does this formula work? Let's break it down so you can see how it works.

CORRECT function

The RIGHT function removes a specified number of characters from the right side of a text string. For example, = RIGHT ("bananas", 4) gives "anas"

LEN function

The LEN function returns the length of a string. = LEN ("apples"), for example, returns 6 because the string "apples" has 6 characters.

RIGHT (A2, LEN (A2)

This component of the formula receives the answer 8. You must specify the text to be used as the first parameter of the RIGHT function. In this case, cell A2 contains the postal code. You must specify the number of characters to be extracted in the second parameter. I use the LEN function for this parameter, which returns the amount of characters in the zip code CV36 7BL, which is 8. A character is defined as the space between CV36 and 7BL. = RIGHT (A2, COUNTY (A2)) = RIGHT (A2.8) = RIGHT (A2.8) = RIGHT (A2.8) = RIGHT (A2.8) = RIGHT (A2.8) = RIGHT (A2, 8) = RIGHT (A2.8) = RIGHT (A2.8) = RIGHT (A

RIGHT (A2, LEN (A2) -4)

I want to delete the first four letters, so I put a -4 at the end of the formula. LEN (A2) -4 therefore gives 4 (8-4 = 4).

If I simplify this further, the function is RIGHT = RIGHT (A2,4) and it returns CV36.

How to remove the first nth character in a string?

To remove the first nth characters from a string, simply change -4 at the end of the formula to the desired number of characters. For example, to eliminate the first three letters of a string, just change -4 to -3. As a result, the formula becomes = RIGHT (A2, LEN (A2) -3) If you want to delete the first two characters, replace -2 with = RIGHT (A2, LEN (A2) -2) and so on.

Use the Excel MID function

The Excel MID function can also be used to eliminate the first four characters from the zip code CV36 7BL. Assume once again that the zip code is in cell A2 and the formula in cell B2.

= MID (A2,5, LEN (A2)) is the new formula.

So, exactly how does this formula work? I will go through each section of the MID formula.

MID function

The Excel MID function extracts the center of a text depending on how many characters are given. = MID ("bananas", 3.2), for example, gives "na." The first option is the text string or cell reference from which you want to extract information. The first character you want to delete is the second parameter. The third entry indicates the amount of characters to be extracted.

= MIDDLE (A2.5

This component of the formula indicates that the formula should start with the fifth character of the zip code CV36 7BL. This means that it starts with spaces, because spaces are the fifth character from the left.

LEN (A2)

The LEN function returns the number of characters in the CV36 7BL zip code, which is 8.

= MIDDLE (A2; 5; LEN (A2))

When this formula is simplified, the MID function is = MID (A2,5,8). It starts with a space and continues to extract 8 characters. Since there are only three characters after the space, it extracts 7BL.

How to remove the first nth character in a string?

To eliminate the initial nth character, simply enter a 1 in the second parameter of the MID function. For example, if I want to delete the first three characters, I insert 4 in the second parameter of the MID function, which makes it = MID (A2,4, LEN (A2)). If I want to delete the first two letters, I simply enter 3 for the second parameter, which makes it = MID (A2,3, LEN (A2)).

Using the Excel REPLACE function

Continues on the topic to eliminate the first four characters of the zip code CV36 7BL. I will now teach you how to do it in Excel using the REPLACE function. I assume the zip code is in cell A2 and the formula is in cell B2.

The formula in cell B2 is now = REPLACE (A2,1,4, "")

I'll now show you how this formula works.

REPLACE the function

The REPLACE function replaces one string character with another string character. The first entry of the replace function is the string or cell with which you want to replace characters, such as the zip code in cell A2. The second entry is the place of the previous text to start replacing characters. The third entry indicates the amount of characters to use to replace the existing content. The fourth option specifies the new characters to be used instead of the old text.

REPLACE (A2; 1; 4, "")

The first parameter is the postal code of cell A2. The start number is the second parameter. I would like to start from the beginning, so I type 1. The third entry is 4, which indicates that I want to replace the first four characters with new content. The last parameter consists of two quotation marks, which represent empty strings. I want to replace the first four characters with empty strings and just leave the last three characters.

How to remove the first nth character in a string?

Simply modify the third argument with the number of characters you want to delete to delete the first nth character. For example, if you want to delete the first three characters, just replace the third parameter with 3, resulting in = REPLACE (A2,1,3, "").

I hope you enjoyed this Excel tutorial on how to delete the first four characters. Leave a comment on my website if you have any questions or if you know of other techniques for editing text in Excel.