Remove First Character from String in Excel

Working with text data would often require slicing and dicing it.

And one such common thing people often have to do is to remove the first characters from a string in Excel.

Suppose you have a dataset as shown below (in column A) and you want to remove only the first characters from each cell and keep the rest of the text as is (resulting data shown in column B).

There are multiple ways to do this in Excel…

And in this tutorial, I will show you different ways to remove the first characters from a string in Excel.

So let’s not waste any time and get to the point.

Replace the First Character with a Blank Using a Formula

One of the easiest wat to remove the first character from a text string in a cell is to replace it with a blank (null character).

And you can easily do this using the REPLACE function.

Suppose you have the dataset as shown below and you want to remove the first alphabet from all these cells.

The below formula will do this:

=REPLACE(A2,1,1,"")

The above formula simply starts from the beginning and replaces 1 character from the beginning with a blank (“”).

You can also use this to remove multiple characters from each cell. For example, if you want to remove the first two characters, you can use the below formula:

=REPLACE(A1,1,2,"")

Extract Everything Except the First Characters from a Cell (using a formula)

Excel has a lot of Text functions and you can do the same thing in many different ways. In the above method, we replaced the first character with a blank, and in this method, we will use a formula to extract every character from a string except the first one.

This can be done using the RIGHT function (which extracts the given number of characters from the right of a text string).

Suppose you have a dataset as shown below:

Here is another formula method to do this by extracting everything except the first character from the cells.

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

The above formula uses the LEN function to first find out the total number of characters in the cell. It then uses the RIGHT function to get all the characters as a result of the formula, except the first one.

Also read: Remove Last Character from Cells

Use Text to Column to Split the First Character and the Rest of the Text String

The above two formula methods would require you to use an extra column and give the result in that extra column.

Here is a method that uses Text to Columns in Excel and allows you to choose whether you want the result in the same cell or in a separate cell.

Suppose you have a dataset as shown below and you want to remove the first character and get all the remaining characters.

Below are the steps to do this using Text to Columns in Excel:

1. Select the cells from which you want to remove the first character in Excel
2. Click the Data tab
3. In the Data tools, click on Text to Columns
4. In the Convert Text to Column Wizard, make the following changes:
   • Step 1 of 3: Select Fixed width (by default Delimited is selected so you need to change this) and click on Next
   • Step 2 of 3: In the Data preview, place the cursor after the first character and right-click. This will insert a line as shown below. This line tells Text to Column to split the text into separate cells
   • Step 3 of 3: Select Do not import column (skip) and keep the destination cell as is (which would be the same cell where you have the data).
5. Click on Finish.

The above steps would instantly remove the first character from each cell and give you the rest.

Let me quickly explain how this works:

When you place the cursor and click after the first character in Step 2, Text to Columns was told to split the data using that line. So the characters before the line are split as one part and the rest as another part.

But in Step 3, since we selected the first column (which was to the left of the line we inserted in Step 2) and then selected ‘Do not import column’, it simply skipped the first character and gave us the remaining part in the same cell.

While I have used this method to remove the first character in Excel, you can use it to remove a second, third, or nth number of characters from the beginning of the end. You can also use this method to extract 2/3/n number of characters from the beginning or the middle of a text string.

Use Flash Fill to Instantly Remove the First Character

I love the Flash Fill feature in Excel as it makes it so easy to do some level of text data manipulation.

And what we are trying to do here, Flash Fill is a perfect tool.

Suppose you have the dataset as shown below and you want to remove the first characters from each cell.

Here are the steps to do this:

1. In a cell adjacent to the dataset’s first cell, enter the result you want. In this case, since I have M70 and I want to remove the first character, I will manually enter the value 271.
2. In the second cell, enter the expected result, which would be 360 in this example. [While you’re typing, you may see some values in gray. These are values Flash Fill guessed based on the pattern. If these are correct, stop typing and just hit the enter key and you will get the result. In case these values don’t show or disappear, move to the next step]
3. Select both the cells, place the cursor at the bottom-right part of the selection (at the small green square) and double-click (or hold the left key of the mouse and drag till the end of the dataset).
4. At the bottom of the resulting data, you will see a small ‘Auto Fill Options’ icon. Click on it.
5. Click on Flash Fill

That’s it!

You will see Flash Fill has automatically identified the pattern and now gives you all the characters from a cell except the first characters.

There are some really cool things you can do this Flash Fill and I cover those in this video below:

Use a Simple VBA Macro in the Immediate Window

An immediate window is a place in the Excel VB Editor that allows you to quickly run a macro code (without getting into the hassle of inserting a module or saving the macro)

All you need to do is use the code, copy and paste it in the immediate window and hit the enter key.

Another great thing about this method is that you don’t need to use another column to get the result. As soon as you run the code, it gives you the resulting data in the same cells.

Below is the line of code I will be using to remove the first character from each cell in a selected range:

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

Here are the steps to use this code from the immediate window:

1. Select the cells/range from which you want to remove the first character from the text string
2. Right-click on the sheet tab name (the same sheet where you have this data)
3. Click on View Code. This will open the VB Editor backend
4. In the VB Editor window, click on the View option in the menu and then click on Immediate Window. This will make the immediate window show up. This step is not needed in case the immediate window is already visible.
5. Copy and paste the above line of code in the immediate window
6. Place the cursor at the end of the line
7. Hit the Enter key

The above steps would instantly run the code on the selected data and remove the first character from each cell.

A quick explanation of the line of VBA code

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

The above line of code uses a For Next loop that goes through each cell in the selection. It then uses the RIGHT and LEN function to extract all the characters, except the first one.

These are the five simple methods you can use to get rid of the first character from a text string and extract the rest. You can choose what method to use based on your data set and your requirements.

For example, if you don’t want to use an extra column and want to get the results in the same cells, you’re better off using the Text to Columns method or the VBA Immediate Window method.

I hope you found this tutorial useful.

You may also like the following Excel tutorials:

• Remove Leading, Trailing, and Double Spaces in Excel
• Separate First and Last Name in Excel (Split Names Using Formulas)
• Convert Text to Numbers in Excel
• Extract Numbers from a String in Excel
• How to Remove Comma in Excel (from Text and Numbers)
• How To Remove Text Before Or After a Specific Character In Excel
• Remove Characters From Left in Excel