How to remove the first character in Excel

Many organizations widely use Excel for their office use. Data entered by the user is a combination of numeric values, alphabets, symbols, etc. The data are calculated based on the requirement. Sometimes the data needs to be edited for calculation purposes. Editing includes removing the specified characters or letters in the cell. The editing job is done manually for a small amount of data, but it is a tough and time-consuming process for a large amount of data. Excel provides various functions and methods to calculate the data quickly and efficiently to rectify this issue. The various methods to remove the first character or letter in the data are clearly explained.

Right and Len function

The Excel function called RIGHT and LEN is used to remove the first character in the specified cell. The steps to be followed are,

Example 1: Remove the first letter in the cell using the Excel function

Step 1: Enter the data in the worksheet, namely A1:A5

Step 2: Select a new cell, B1, where the user wants to display the result and enter the Formula as =RIGHT (A1, LEN (A1)-1).

Step 3: Press Enter. The result will be displayed in selective cell B1.

From the worksheet, the RIGHT and LEN function removes the first letter in the cell. To get the result for the remaining data, drag the Formula towards cell B5, and it returns the last four characters from the text string by removing the first character using Formula. Similarly, this method is used to calculate other data. To remove more than one character, based on the requirements 2, 3, etc., is used in the Formula to subtract from the value displayed by the LEN function.

Working process of Formula

The LEN function counts the total number of characters in cell A2. Hence the result will be =RIGHT (A2, LEN (5-1)). 5-1=4. Hence the Formula will be as follows, =RIGHT (A2, 4). The RIGHT function extracts the last four characters from the cell and skips the last character from the Right and the first from the left.

Replace Function

The Excel function called REPLACE is used to remove the first character in the specified cell. It replaces the first character of the text string with empty spaces.

Example 2: Remove the first letter in the cell using the Excel function

Step 1: Enter the data in the worksheet, namely A1:A5

Step 2: Select a new cell, B1, where the user wants to display the result and enter the Formula as =REPLACE (A1, 1,").

Step 3: Press Enter. The result will be displayed in selective cell B1.

The REPLACE function replaces the first character or letter with an empty string in the worksheet. To get the result for the remaining data, drag the Formula toward cell B5, as it replaces the first character with an empty string.

Working process of Formula

Here in the Formula, =REPLACE (A1, 1, 1,"), A1 indicates the cell containing data, 1 represents the first String, and the other 1 represents the only one character that should be replaced. It replaces only the first character or letter, as the remaining letters are unchanged. Similarly, this method is used to calculate other data. To replace more than one character based on requirements 2, 3, etc., is used.

MID and LEN Function

To remove the first character in the specified cell, the Excel function MID and LEN are used together in the data. It removes the first character present in the Text.

Example 3: Remove the first letter in the cell using the Excel function

Step 1: Enter the data in the worksheet, namely A1:A5

Step 2: Select a new cell, namely B1, where the user wants to display the result and enter the Formula as =MID (A1, 2, LEN (A1)-1)

Step 3: Press Enter. The result will be displayed in selective cell B1.

In the worksheet, the MID and LEN function removes the first character or letter in the String. To get the result for the remaining data, drag the Formula towards cell B5, as it simply removes the first character with an empty string.

Working process of Formula

Here in the formula, =MID (A1, 2, LEN (A1)-1)

A1 indicates the cell containing data, LEN function returns the number of characters in the cell. Hence the Formula is like =MID (A1, 2, LEN (5-1).

5-1=4, therefore =MID (A1, 2, LEN (4))

The number '2' represents the starting position of the data. Hence the Formula returns the remaining characters except the first. Similarly, this method is followed for other data.

Text-to-Column Method

Excel provides a method called Text to a column, which removes the first character or letter present in the cell. This method simply removes the first character present in the cell and displays it in a separate column. The remaining data are displayed in a separate column without deleting the first character.

Example 4: Remove the first character from the cell using the Text to Column Method.

Step 1: Enter the data in the worksheet, namely A1:A5

Step 2: Select the range of columns from A1:A5, then choose Text to columns in the Data tab option.

Step 3: Convert Text to Column Wizard will display. In that, choose the option called Fixed Width. Click Next.

Step 4: Covert Text to Columns Wizard will open. To insert a vertical line between the characters, click between the first and second characters in the preview section. Click Next.

In the data column format, select the required format and click Finish.

Step 5: The Text to Column method displays the first and other characters in a separate cell.

Here in the worksheet, the Text to Column Methods displays the first character in the row A1:A5 and the remaining characters in the cell B1:B5. If not needed, the user can delete the first column with the first character.

Note: It is not a dynamic method. If one more character needs to be separated, the vertical line should be adjusted.

Flash Fill Method

One of the methods to remove the first character or letter in the cell is Flash Fill Method. The shortcut keys for applying Flash Fill Method are Ctrl+E. It is one of the easiest and most efficient methods without using a Formula. The steps to be followed are,

Example 5: Remove the first character or letter from the cell using Flash Fill Method.

Step 1: Enter the data in the worksheet A1:A5

Step 2: In cell B1, enter the data without the first character. Press Ctrl+E for Flash Fill Method.

Step 3: The Flash Fill Method displays the result for the remaining data, which follows the first method present in cell B1.

In the worksheet, the Flash Fill Method follows the pattern present in cell B1, and the result is generated without the first character.

User Defined Function

The user-defined function involves the code and Formula to generate the result. The steps to be followed are,

Example 6: Remove the first character or letter in the data using Flash Fill Method.

Step 1: Enter the data in the worksheet, namely A1:A5

Step 2: Select the column range from A1:A5. Press Alt+F11 to open the basic visual editor in the workbook.

Step 3: In that, insert a module and enter the code as follows,

Public Function remove first (rng As String, cnt As Long)

removeFirstC = Right (rng, Len (rng) - cnt)

End Function

Step 4: In cell B1, enter the formula as =removeFirstC (A1, 1). Here A1 indicates the cell value and 1 indicates the number of characters to be removed.

Step 5: The result will be displayed in cell B1. To get the result for the remaining data, drag the Formula toward cell B5.

Working process of Formula

The cell needs to be referred to in the Formula, or the text value can be directly entered into the function. '1 indicates the number of characters to be removed. If more than one or more characters need to remove, enter the number in the Formula based on the requirement.

Summary

The methods and functions in this tutorial are clearly explained with formulas. For the data set works efficiently and quickly. These methods are convenient to use and dynamic too. The Formula needs to be modified based on the requirement to remove more than one character. It is based on the user's choice of preference, the method to be selected for retrieving the result from the data.