This tutorial will show various ways and tips on how to remove the first character from a cell in Excel using formulas.
In general, you can solve all text and string manipulation challenges with the help of custom formulas that are based on one or more built-in functions.
This article is a part of our definitive guide on Excel Formulas.
Table of contents:
- Remove the first character using the REPLACE function
- Use MID and LEFT functions to get the remaining part of a string
- RIGHT and LEN functions
- Apply a user-defined function (UDF)
- Use DataXL add-in
- Text-to-columns function
How to remove the first character in Excel?
Steps to remove the first character from a cell:
- Apply the =REPLACE(text, 1, 1, “”) formula where the arguments are:
- old_text: the cell that contains text
- start_num: character position = 1
- num_chars: no. of characters you want to replace = 1
- new_text: the text you want to replace = “”
Using the setup mentioned above, REPLACE function replaces the first character in cell B3 with an empty string “” and returns the result.
Remove the first n characters
Sometimes, you want to strip more than one character from the left of a string. In this case, use the following formula:
In the formula, “n” is an arbitrary number. For example, if you want to remove the first five characters from the left, use the formula below:
Get the remaining part of the string using MID and LEFT
We use the MID and LEFT functions in the example to extract the string from cell B3 without the first character.
Configure the MID function arguments in the following way:
- text = “B3”
- start_num = 2
- num_chars = LEN(B3)-1
The formula will extract the text from the second character and returns the part of a string.
Apply RIGHT and LEN Functions to remove the first character
You can combine the RIGHT and LEN functions to delete the first N characters from the left.
Evaluate the formula from the inside out. First, the LEN function will return the length of the selected cell. After that, we extract the “text_lenght – 1” characters from the right using the RIGHT function.
In the example, you want to remove three characters from the left:
Apply a UDF (User defined function) to remove the first character
If you are familiar with VBA, you can write a simple function:
Function RemoveFirstNChar(rng As string, counter As Long) RemoveFirstNChar = Right(rng, Len(rng) - counter) End Function
To implement the RemoveFirstChar function, press Alt + F11 to open the VBA editor, then insert a new Module.
Finally, paste the code and save the Workbook as .xlsm.
Use DataXL Productivity Suite
We have a powerful add-in section to extend the built-in function library.
- First, go to the DataXL tab on the ribbon. Then, select the cell or range of cells that contains the text.
- Click on the Text Tools icon. A new window will appear.
- Select the Delete Tab and enter a number value, in this case, 1.
Use Text-to-columns function
Using the Text-to-Columns function, you can split a single text column into multiple parts.
First, click on the Ribbon and locate the Data Tab.
After clicking the Text-to-Columns icon, a new window will appear:
Choose the “Fixed width” option, then click “Next”.
Move the cursor over the “Data Preview” area to separate the string’s first character manually.
Click next to change the data format, or click “Finish” to perform the action.