Remove First Character from String in Excel (4 Ways)

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.

Sample Dataset to remove the first character from 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:

Removing first character from string in Excel using REPLACE function

Using the Fill Handle the result for the rest of the table can be found.

Here,

  • 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:

Removing 2 characters from string

=REPLACE (old text, start_num, num_chars, new_text)

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:

Using Substitute function

Here,

  • 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.
=SUBSTITUTE (text, old_text, new_text, [instance_num])

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:

Using Nested MID and LEN function

Here,

  • 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.
=MID(C5,2, LEN(C5)-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.

Using Combined RIGHT and LEN function

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.

=RIGHT(C5, LEN(C5)-1)

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.

Selecting all the cells where character is to be removed

  • You can find the Find & Select option from the Home Tab. Select Replace from the drop-down menu of Find & Select.

Finding Find and Replace from the Home tab

  • 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.

Placing the character to be removed in find what and replacing all

  • A new box showing the number of replacements done will pop up. Click OK.

Checking for how many replacements done and clicking ok

You will get the result as:

Result using find and replace

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.

Selecting cells for applying text to column from data tab

  • Find the Text to Column from Data Tab and click on it.

Finding text to column from data tab

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.

Selecting fixed width and clicking 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.

Data preview and finishing in step 2 of 3

  • 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.

Result showing splited column

  • 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.

Selecting destination and not to import to column

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.

Two results of using text to column from the data tab

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.

Writing ID without first character in the first cell

Next, using the Fill Handle you will get the desired result.

Using flash fill for filling up the column

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.

Selecting cell for using VBA Macro

  • Afterward, right-click on the sheet name at the bottom of the sheet and choose View Code from the drop-down menu.

Clicking View mode by right clicking on the name of the sheet

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.

Choosing Immediate window from view of VBA window

  • Find the Immediate Window at the bottom and copy and paste the code there.

Code:

For Each cell In Selection: cell.Value = Right(cell.Value, Len(cell.Value) - 1): Next cell

Code written in the immediate window

  • Take the cursor at the end of the code line and press Enter.

Putting cursor at the end of the code and pressing enter

You will see the result in the Excel worksheet.

Result of using VBA Macro

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.

Conclusion

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.

 

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo