Remove First Or Last Characters By Formula In Excel
With the Excel LEFT and RIGHT function, you can remove the certain characters from the beginning or the end of the strings. Please do as the following steps:
Remove the first four characters from the text string.
Step 1. Type the following formula in adjacent cell B1: =RIGHT(A1, LEN(A1)-4), see screenshot:
Tips: This formula means to return the right most number of characters, you need to subtract 4 characters from left string. And you can specify the number of characters you want to remove from the left string by changing the Number 4 in the formula =RIGHT(A1, LEN(A1)-4).
Step 2. Then press Enter key, and select the cell B1, then drag the fill handle over the cells that you want to contain this formula. And now you are successful in removing the first 4 characters of the text strings. See screenshot:
If you need to remove the last several characters, you can use the LEFT function as the same as the RIGHT function.
Note: Using the Excel function to remove certain characters is not as directly as it is. Just take a look at the way provided in next method, which is no more than two or three mouse clicks.
Remove First Or Last Characters By Find And Replace In Excel
If you want to remove all characters at the front or end of the colon :, Find and Replace function in Excel also can make your removing as soon as quickly.
Step 1. Hold the Ctrl button and press F to open Find and Replace dialog, and click Replace.
Step 2. Enter :* into the Find what box, and leave blank in Replace with box. See screenshot:
Step 3. Click Replace All, and all the characters at the end of the colon (include the colon) have been removed. See screenshot:
If you want to remove all characters before the colon, please type *: into the Find what box, and leave blank in Replace with box. See screenshot:
Click Replace All, all the characters before the colon have been removed. See screenshot:
Note: This method is only applied to the characters which contain the specific separators, so you can change the colon: to any other separators as your need.
https://www.extendoffice.com/documents/excel.html
With the Excel LEFT and RIGHT function, you can remove the certain characters from the beginning or the end of the strings. Please do as the following steps:
Remove the first four characters from the text string.
Step 1. Type the following formula in adjacent cell B1: =RIGHT(A1, LEN(A1)-4), see screenshot:
Tips: This formula means to return the right most number of characters, you need to subtract 4 characters from left string. And you can specify the number of characters you want to remove from the left string by changing the Number 4 in the formula =RIGHT(A1, LEN(A1)-4).
Step 2. Then press Enter key, and select the cell B1, then drag the fill handle over the cells that you want to contain this formula. And now you are successful in removing the first 4 characters of the text strings. See screenshot:
If you need to remove the last several characters, you can use the LEFT function as the same as the RIGHT function.
Note: Using the Excel function to remove certain characters is not as directly as it is. Just take a look at the way provided in next method, which is no more than two or three mouse clicks.
Remove First Or Last Characters By Find And Replace In Excel
If you want to remove all characters at the front or end of the colon :, Find and Replace function in Excel also can make your removing as soon as quickly.
Step 1. Hold the Ctrl button and press F to open Find and Replace dialog, and click Replace.
Step 2. Enter :* into the Find what box, and leave blank in Replace with box. See screenshot:
Step 3. Click Replace All, and all the characters at the end of the colon (include the colon) have been removed. See screenshot:
If you want to remove all characters before the colon, please type *: into the Find what box, and leave blank in Replace with box. See screenshot:
Click Replace All, all the characters before the colon have been removed. See screenshot:
Note: This method is only applied to the characters which contain the specific separators, so you can change the colon: to any other separators as your need.
https://www.extendoffice.com/documents/excel.html
