-->

How to Remove First 4 Character in Microsoft Excel



You may have a workbook that contains text but you only want to extract some characters from it. For example, you might only want to extract the first name from cells that contain the person's first and last name. In this tutorial I will show you how to work with text and in particular, I will show you some cool techniques on how to delete the first four characters in Excel using the following functions:

1) EXCEL RIGHT FUNCTION

2) Excel MID function

3) Excel change function

Use the right function in Excel

In this example, I want to remove the first 4 characters of the CV36 7BL zip and leave the last 3 characters. Let's say the zip code is in cell A2 in an Excel spreadsheet. The formula in cell B2 will be:

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

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

RIGHT .Function

The RIGHT function extracts a specified number of characters from the right side of the selected text. eg = RIGHT("banana", 4) will return "anas"

LEN Function

The LEN function extracts the length of a given string. For example =LEN("apple") will return 6 because there are 6 characters in the string "apple".

RIGHT (A2, LEN (A2)

This will return part of the formula 8. For the first argument of the RIGHT function, you must specify the text you want to use. In this example, cell A2 is the zip code. For the second argument, you must specify the number of characters you want to extract. For this argument, I'm using the LEN function which returns the number of characters for the CV36 7BL zip code which is 8. The spaces between CV36 and 7BL are counted as characters. The formula =RIGHT(A2, LEN(A2)) translates to =RIGHT(A2.8) which returns CV36 7BL.

RIGHT (A2, LEN (A2) -4)

I wanted to remove the first 4 characters so I included -4 at the end of the formula. So it returns LEN(A2)-4 4 (8-4 = 4)

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

How do you remove the first n character from a string?

If you want to remove the first n character in a string, you can simply change the -4 at the end of the formula to any number of characters you want to remove. For example, if you want to remove the first three characters of a string, simply change -4 to -3. So the formula becomes =RIGHT(A2, LEN(A2)-3). If you want to remove the first two characters, change it to -2 so that it becomes =RIGHT(A2, LEN(A2)-2) and so on.

Using the MID function in Excel

Another way to remove the first four letters of the CV36 7BL zip code is to use the MID function in Excel. Assume again that the postal code is in cell A2 and the formula is in cell B2.

The formula is now = MID(A2,5, LEN(A2))

So how does this formula work? I will explain each part of the MID formula.

The MID Function

The MID function in Excel extracts the middle of the text based on the specified number of characters. For example, =MID("banana", 3,2) displays "na". The first argument is the text string or cell reference you want to extract. The second argument is the first character you want to extract. The third argument is the number of characters you want to extract.

= MID (A2.5

This part of the formula indicates starting with the fifth letter of the CV36 7BL zip code. This means that it will start from a space because the space is the fifth character from the left.

LEN (A2)

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

= MID (A2,5, LEN (A2))

If you simplify this formula, the MID function is =MID(A2,5,8). It starts with a space and extracts 8 characters. Since there are only 3 characters after the space, extract 7BL.

How do you remove the first n character from a string?

If you want to remove the first n character, just add 1 in the second argument of the MID function. For example, if I want to omit the first 3 characters, I put 4 in the second argument of the MID function so it becomes =MID(A2,4,LEN(A2)).

 If I want to remove the first two characters, just enter 3 for the second argument so it becomes =MID(A2,3,LEN(A2)).

Using the replace function in Excel

Continuing the topic of removing the first four letters of the CV36 7BL zip, I will now show you how to do it using the REPLACE function in Excel. Again I'm assuming the zip is in cell A2 and the formula is in cell B2.

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

Now I will show you how this formula works.

The REPLACE Function

The REPLACE function replaces one character set in a string with another character set. The first argument of the replace function is the string or cell in which you want to replace the character, which is the zip code in cell A2. The second argument is the position of the old text to start the character replacement. The third argument is the number of characters you want to replace with the old text. The fourth argument is the new character you want to replace with the old text.


REPLACE(A2,1,4,"")

The first argument is the zip code in cell A2. The second argument is the starting number. I want to start from the beginning so I enter 1. The third argument is 4 as I want to replace the first 4 characters with new text. The last argument is two quotation marks which mean empty strings. I want to replace the first 4 characters with empty strings so I am left with the last 3 characters.

How do you Remove the First nth Character of a String?

To remove the first nth character just replace the third argument with the number of characters you want to remove. For example, if you want to remove the first 3 characters then just change the third argument to 3 so it becomes =REPLACE(A2,1,3,"").

Berlangganan update artikel terbaru via email:

0 Response to "How to Remove First 4 Character in Microsoft Excel"

Post a Comment

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel