Many times, you need to remove the first character from a string of your database in Excel. The article will help to know 4 different ways to remove the first character from a string in Excel.
4 Ways to Remove First Character from String in Excel
The dataset below will be used for explaining 4 different ways of removing the first character from a string in Excel.
Download Practice Workbook
If you want to practice you can download the practice workbook from here.
The dataset contains the ID along with the serial numbers of the employee of the IT department of a company. Previously they used “#” at the start of their ID number, but now they want to remove this and keep the rest as the ID. Let us find out how they can do so using the 4 ways below.
1. Removing First Character from String Using Formulas
You can remove the first character from a string using various types of formulas. Those are evaluated below.
a. Using REPLACE to Remove First Character from String
You can use the REPLACE function to replace the first character. The syntax of the function:
REPLACE (old text, start_num, num_chars, new_text)
For the given dataset, to remove “#”, the result will be as follows:
Using the Fill Handle the result for the rest of the table can be found.
- C5 indicates the cell containing the original text.
- First 1 means the start of the number of texts to be removed.
- Second 1 means the number of texts to be removed
- The double quotes (“”) indicate the character “#” to be replaced by a blank.
For the second 1 in the function, you can increment the number to remove the more than one character from the string. Let’s say we want to remove 2 characters, then using 2 instead of 1 in the 3rd argument of the function we will get the result as follows:
b. Using SUBSTITUTE To Replace the First Character of a String with a Blank
You can also use the SUBSTITUTE function to substitute the character with a blank.
The syntax for SUBSTITUTE function:
SUBSTITUTE (text, old_text, new_text, [instance_num])
Instance number is an optional thing.
For the given dataset, using this function we will get the result as follows:
- C5 indicates the cell where the removal of character is required
- The second argument of the function indicates the character to be removed. In this case, it is “#”.
- Third argument is the text to be substituted by the previously given. As we want to just remove the character, so it is kept blank here.
c. Using Nested MID and LEN to Remove the First Character from String
Now let’s look at a formula combination of MID and LEN functions. For details regarding the MID and LEN function you can go to How to Use MID Function in Excel (3 Examples) – ExcelDemy / How to Use LEN Function in Excel (Formula and VBA Code) – ExcelDemy
The result by using the nested formula is shown below:
- C5 is the cell address with which the function is working.
- 2 is the starting number of your characters which you want to keep.
- LEN contains the cell address.
- -1 is subtracting the number of characters to be removed, which in our case is 1.
d. Using Nested RIGHT and LEN to Extract All Characters in a Cell Except the First
Again, nested RIGHT and LEN can be used to remove the first character from a string.
The result is shown below.
Here the arguments are like our previous method, 1(c), the only difference is that the starting number is not required since RIGHT indicates that the starting character number will be right after the removed character number.
2. Removing First Character from the Ribbon Tabs in Excel
Furthermore, you can remove the first character of the string from the options available in the Ribbon Tabs in Excel.
a. Using Find and Replace from the Home Tab
Starting with, Find and Replace which is found in the Home Tab.
Follow the steps to know how to do this:
- Select the cells where you want to remove the first character.
- You can find the Find & Select option from the Home Tab. Select Replace from the drop-down menu of Find & Select.
- A new box named Find and Replace will open. In Replace write # in the Find what section and keep the Replace with section blank. After that, click Replace All.
- A new box showing the number of replacements done will pop up. Click OK.
You will get the result as:
b. Using Text to Column from the Data Tab
To remove the first character from a string in Excel, you can use Text to Column from the Data Tab.
Let us remove the “#” by following the steps below:
- First, select the cells.
- Find the Text to Column from Data Tab and click on it.
A box named Convert Text to Column Wizard- Step 1 of 3 will appear.
Step 1 of 3: Choose Fixed width and then click on Next.
Step 2 of 3: In Data preview click just after the first character (you can click after the characters you want to remove). An arrow line beside the first character is created. Then click on Finish.
- You will notice that the string splits into two columns, one with the first character and the rest of the characters in the column beside it.
- Now if you want to see the results in the same column and remove the first character fully. From Step 2 of 3 Select Next instead of Finish. This will take you to Step 3 of 3.
Step 3 of 3: Select Do not import column(skip) and check whether the Destination is showing the address of your selected data or not. If not, then select or write the cell address as an absolute cell reference. Finally, Select Finish.
You can see the result shows the ID removing the first character #. Below picture shows both the results together below. You can use any one according to your requirements.
3.Removing First Character Using Flash Fill
Flash Fill is an easy and time-saving way of removing the first character from a string.
You must write the string in a new column without the first character.
Next, using the Fill Handle you will get the desired result.
Note: For safety and accurate result right-click on the symbol showing up after using Fill Handle and choose Flash Fill from there.
4.Removing First Character Using Simple VBA Macro in the Immediate Window
We will be moving towards the last method of the article. This method uses a Simple VBA Macro in Immediate Window to remove the first character in the string. Below are the steps:
- Select the cells.
- Afterward, right-click on the sheet name at the bottom of the sheet and choose View Code from the drop-down menu.
Note: If it is not working you might need to turn on the developer tab. Hence, right-click anywhere on the Ribbon tabs of Excel and go to Customize the Ribbon. In the Main tabs check the Developer Tab and a Developer Tab will appear in the Ribbon.
- A VBA window will open. The immediate window should appear at the bottom of the page. If you cannot find, then go to View and select Immediate Window.
- Find the Immediate Window at the bottom and copy and paste the code there.
For Each cell In Selection: cell.Value = Right(cell.Value, Len(cell.Value) - 1): Next cell
- Take the cursor at the end of the code line and press Enter.
You will see the result in the Excel worksheet.
Things To Remember
However, if you want to apply the formula or other methods in the same column containing your original data in that case, you will lose the original data. For safety, it is good to keep the original data by copy and pasting it to a different place in order that you can use them when required.
The article demonstrated 4 different methods of removing the first character from a string. Briefly, the 4 different ways include formulas(REPLACE, SUBSTITUTE, Nested MID and LEN, Nested RIGHT and LEN), Ribbon tabs, Flash Fill, and VBA Macro in Immediate Window. If you want to remove multiple characters, you can follow the changes mentioned in the article.