How to Remove First Character in Excel?

by

Excel is an excellent choice for storing and managing small databases efficiently.

However, sometimes in order to clean Excel raw data and extract some meaningful information from a data set, we have to remove first or some characters from the start of a cell or range of cells.

Doing so is easy when we have a small data set, but it gets difficult to remove the first character from a large data set manually.

So in this tutorial, I will show you how to remove the first character from the data set in Excel. Apart from that, I will also share some quick tips and tricks as well.

So let’s discuss each method in detail.

Method 1: Remove the First Character Using Flash Fill Option

In Excel, there is a fantastic feature that automatically fills the cell by following a pattern.

It’s called Flash Fill.

For this, you have to fill the first few cells based on some pattern, Excel will pick that pattern and automatically fill the remaining cell for you based on that pattern. 

So let’s see how we can remove the first character using the Flash Fill option.

I will use sample data to demonstrate this method, as shown below. 

Dataset from which we want to remove the first character

In the Data column, there is an extra character at the start of each cell’s data that I want to remove.

Let’s see how we can remove this extra first character using the Flash Fill option in the ribbon. 

  1. To train Excel, manually enter the result you want in cell B2 by removing the first character, as shown in the screenshot.
enter the result manually in cell B2
  1. Click on any cell in the result column. Let’s say I am going to select cell B3
select any other empty cell
  1. Click on the Data tab in the ribbon.
select the data tab
  1. Click on the option Flash Fill or hit CTRL + E from the keyboard
click on the flash fill icon

This will automatically follow the pattern in step 1 and fill all the remaining cells accordingly. You can see the output in below screenshot

resulting data from which the first character has been removed

Tip: This is the easiest way using you can use to remove the first character in Excel quickly, but keep in mind you can not use the Flash Fill method to fill cells horizontally. Also, it’s possible that Flash Fill may not recognize the pattern correctly, so check the results to ensure that the output is correct.

Also read: How to Convert to Sentence Case in Excel?

Method 2: Remove First Character Using RIGHT & LEN Functions

In this section, I will show you how to employ the RIGHT and LEN functions together to remove the first character. 

I will use the below sample data to demonstrate this method, as shown below. 

dataset

The Data column has an extra character at the start of each cell’s data, and I want to remove this first character from each cell.

Let’s see how we can remove this unnecessary first character using the RIGHT and LEN functions.

We will use the formula below to remove the first character from the above data set.

RIGHT(text,LEN(text)-N)

Where N is the number of characters that we want to remove from the start of the data set.

As in this case, we want to remove just the first character, so the formula that we need to enter in cell A2 will be:

RIGHT(A2,LEN(A2)-1)
enter the right formula in cell B2

Now copy the formula in the entire column to see results for all the other cells in column A. 

copy the formula for the entire column

Now let me explain how this formula works.

RIGHT is a built-in Excel function that returns the specified number of characters from the end of the data set.

However, the LEN function returns the total number of characters in the dataset. 

In the above case, we want to remove just one character, so the below steps explain the working of the formula:

  • LEN(A2) – 1 = 3: This gives us the length of the text in the cell after removing the first character
  • RIGHT (A2, LEN (A2) – 1) becomes RIGHT(A2, 3)
  • RIGHT (A2, 3) – this extracts the last three characters from the cell

Using the above formula, you can remove any number of characters from the start of the data set.

Let’s say you that want to remove the first three characters, so in that case the formula would become

RIGHT(text,LEN(text)-3)

Method 3: Remove First Character Using REPLACE Function

In this method, I will show you how you can use REPLACE function to remove the first character from the data.

We can do so by replacing the first character with a blank one (it is similar to removing that character from the cell).

I will use sample data to demonstrate this method, as shown below. 

enter the replace formula in cell B2

The Data column has an extra character at the start of each cell’s data.

Let’s see how we can remove this unnecessary first character using REPLACE function.

Below is the generalized formula I am going to use

REPLACE(old_text,start_num,num_chars,new_text)

So using the above formula, we can replace the first character with a blank character. For that, the formula for cell A2 would become

REPLACE(A2,1,1," ")

Now copy the formula in the entire column to see results for other data 

copy the formula for the entire column

How the formula works

In the above formula

  • text = A2 would become text = T512
  • start_num = 1: it means starting from the first character “T”
  • num_chars = 1: we want to replace just one character, so the number of characters is 1, which is “T” (the first character)
  • new_text = “ ”: we want to remove the first character so the character “T” would be replaced with new text, which is a blank character “ ”

Tip: This formula can also be used to remove more than one character from the start, end, or middle of the data set. For example, let’s say we want to remove the first 2 characters from the start of the data set then, the formula would become

REPLACE("text", 1,2, " ")

Also read: Remove the Last 4 Characters in Excel

Method 4: Remove First Character Using Text to Columns

The first 3 methods require an extra column to display results but using this method, you can display the result separately or in the same column.

This method uses the Text to Columns option in the ribbon to remove the first character.

Text to Columns is an excellent feature that splits the cell value of one or more cells based on a delimiter or fixed width. Let’s see it in practice.

I will use sample data to show this method, as shown below. 

data set

The Data column has an extra character at the start of each cell’s data.

Let’s see how we can remove this unnecessary first character using the Text to Column option.

I will show the result in the same data column, so there is no need to create an extra column. Now follow the below steps to get the complete procedure.

  1. Select the entire data from which you want to remove the first character
select the entire data set
  1. Click on the Data tab in the ribbon
click the data tab
  1. Click on the Text to Column option in the Data Tools section
click on text to columns

This will open the following dialog box, which is 3 step process to remove the first character

step one of convert text to columns wizard
  1. Step 1 of 3: Check the Fixed Width checkbox and click on the Next
select the fixed width option
  1. Step 2 of 3: Place the cursor after the first character and left-click from the mouse in the Data preview option. This will insert a line after the first character of each value, as shown in the below screenshot. After that, hit the Next option.
place the cursor and insert the line
  1. Step 3 of 3: Check the do not import column (skip) option.
select do not import column

Make sure the Destination is the same cell where we have data (because we want the result in the same column, however, you can provide a separate destination cell as well)

select the destination cell

Similarly, make sure that the first character is highlighted in black color. Here Excel will skip the black portion in the final results. 

data preview in convert text to columns wizard
  1. Click on Finish
click on finish

This will remove the first character from all the data sets, as shown below

Data where first character has been removed

Tip: You can use this method to remove any number of characters from the start or end of the text. Just insert the line from where you want to remove the character. 

In this tutorial, I showed you the different methods for removing the first character in Excel.

Method 1 uses the Flash Fill option in the ribbon, methods 2 and 3 use different build-in formulas, while method 4 uses the Text to column option. 

Apart from that, I shared some quick tips and tricks as well.

Other articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment