How to Remove First Character in Excel (Many Examples)

Importing data into Excel from external sources?

Or just got a sheet that’s on you to sort but, the data looks super unstructured. This and many more reasons might make you want to remove the first character (or some beginning characters) from cells in Excel to shape the data.

Some of the times, this might also be important to extract the relevant information and parse data. If you’re in the same situation, this guide has your back 💁‍♀️

We have brought together many ways explained through different examples that you can use to remove the first character from any given text string in Microsoft Excel.

Grab your free practice workbook for this guide here and continue reading until the end to learn them all.

Remove the First Character using the Flash Fill feature

The Flash Fill feature of Excel picks the pattern of how you’re populating data in cells and then automates the data population for the rest of the cells based on this pattern.

That’s smart. You can leverage the Flash Fill tool of Excel to remove the first character of a given dataset in Excel. The best part: this exercise can be automated for thousands of cells at once.

Wanna see how? Let’s try and do it for the following data in Excel 🧪

Data in Excel sheet

To remove the first characters from each of these words:

Step 1) Activate a cell adjacent to the first cell containing the text string.

Step 2) Write the text in the first cell by removing the first character from it.

For example, the text string in Cell A2 “AApple” becomes “Apple”.

Rewriting the first text string

Step 3) Press Enter.

Doing this step draws a pattern for Excel to follow (i.e., copy the text string in the adjacent cell and remove the first text character from it).

Step 4) Select the cells in the adjacent column (Column B) till where the data continues in the first column (Column A).

Selection of cells in worksheet

Step 5) Go to the Home Tab > Editing Group > Fill button > Flash Fill.

Flash Fill button

Or use the keyboard shortcut of the Ctrl key + E.

Kasper Langmann, co-founder of Spreadsheeto

If Excel has sensed the pattern, it will auto-populate the remaining cells following the same pattern, and you will have the first characters removed from the text string in each cell 📏

First character removed

Alternatively, after you have written the text string by removing the first character in the first cell as below.

Rewriting the first text string

Step 6) Do the same in the next cell.

As soon as you write the first or at max two characters in the next cell following the same pattern, Excel will sense it and show you a greyed-out preview of auto-populated data for the remaining cells.

Greyed out preview

Step 7) Press Enter if Excel has rightly sensed the pattern.

The Flash Fill tool will automatically populate the remaining cells by removing the first character from each text string 🤯

Flash-filled lists

The Flash Fill tool is crazy helpful when it comes to data manipulation.

Remove the First Character with the REPLACE function

We can also remove the first character from a cell in Excel using the REPLACE function of Excel.

Just like the name suggests, the REPLACE function takes a text string and replaces a certain string from it (that you specify) with another substring (which is again specified by you) 🙈

We will smartly tweak it to remove the first character from the supplied text string through the following steps:

Step 1) Begin writing the REPLACE function.

Click to copy
Excel functions

Step 2) The first argument requires the text string within which replacements are to be made. Refer to the cell containing the original text string in its place.

Click to copy
The old text cell reference

Step 3) Next is the start_num argument that specifies the text string from where the replacement needs to begin. Will be 1 in our case.

Click to copy
The start Num in Excel formulas

Step 4) The num_chars argument refers to the number of characters to be replaced. We only want the first character removed so it will again be 1.

Click to copy
Num_chars argument after comma

And then the new_text. This argument requires users to specify the text string with which the old text is to be replaced 🏳

We don’t want anything replaced but only the first character removed so we will supply an empty string in its place.

Step 5) Write it as an empty text string (two double quotation marks).

Click to copy

Step 6) That’s it. Press Enter.

The REPLACE function works

Step 7) Drag this formula down the list.

First character split

Excel removes the first character from the text in each cell based on the REPLACE function written above.

Until now, we have seen two methods to remove the first character from the text in a cell in Excel. On to the next.

Remove the First Character using the RIGHT & LEN function

How about we replace the REPLACE function with some other functions?

The same output that we got through the REPLACE function can be achieved through the combination of the RIGHT and LEN functions 🚀

Let’s try and see how.

Step 1) Write the LEN function for the text in Cell A2.

Click to copy
LEN function finds length of the string

LEN stands for length and the LEN function finds the length of a text string.

For Cell A2 (that contains the text string AApple), it returns 6 which is the count of characters in it.

Step 2) Deduct 1 from it.

Click to copy
Deducting 1 from length in Excel cell

Step 3) Nest the above function in the RIGHT function through the following formula:

Click to copy

The RIGHT function is used to extract a given number of characters from the right side of a given text string.

The first argument (A2) refers to the text string from where the characters are to be extracted.

The second argument specifies the number of characters to be extracted. To automate the calculation of the number of characters, we have nested the length function with the deduction of 1 💪

As a result, we get the text string extracted with the first character removed.

Extract text from the right

Step 4) Drag this formula down the list.

All first characters removed

For the whole list, the first character from each cell is removed.

Remove multiple characters

Here’s a little extra information.

While we are exploring ways to remove the first character from each cell in Excel, the above methods can also help you remove any number of characters from any cell in Excel 🏸

For example, if instead of only the first, you want to remove the first three characters from each cell, write the REPLACE function as follows:

Click to copy
The first three specific characters removed

Similarly, the RIGHT and LEN functions become:

Click to copy
The first three characters removed

3 in the above formulas is just a placeholder. It can be any number of characters that you want removed from the beginning (or the left) of a text string in Excel 3️⃣

Pro Tip!

If instead of removing characters from the beginning of the cell, you want to remove last characters from the end (or right) of the cell, replace the RIGHT function with the LEFT function.

It extracts a given number of substrings from the left of a text string.

Convert the result from text to number

Not only text strings but you’d often need to remove the first character from a number in Excel.

For example, for the following list of numbers:

List of numbers

If I use the REPLACE function to remove the first numeric character from each of these numbers:

Click to copy
REPLACE function replaces a number

The first number would be removed.

But, the format of the removed number would be Text / General 🎯

This would have been the same even if I had used the RIGHT & LEN functions as below.

Click to copy
Text format

Values formatted as text are left-aligned, whereas, numbers are right-aligned.

Kasper Langmann, co-founder of Spreadsheeto

You can change the format of the returned value from Text / General to the number format by nesting it into the VALUE function.

The VALUE function converts a text string that in essence is a number, into a number.

Here’s how to do it:

Step 1) Write the VALUE function:

Click to copy
VALUE function

Step 2) Nest the REPLACE function into it.

Click to copy
VALUE function returns value

Step 3) Or, nest the RIGHT & LEN function as below:

Click to copy
Result changed to VALUE

There it is, now the result is formatted as a number (see the right alignment) 🍸

However, this method is only to be used with numeric strings because the VALUE function converts text strings (that are essentially numbers) into numbers.

If the supplied text is essentially text, it would return the #VALUE! error.

Conclusion

This tutorial pulls together a bunch of ways to structure messed-up data in MS Excel.

You’d often need smart hacks to remove the first character from a given set of text strings in Excel to parse your data or to retrieve relevant information to process it further.

Additionally, some other useful hacks to bring data to shape in Excel include counting characters in a given cell, extracting substrings from the left, right, or mid of a text string, and much more.

Check them out in the linked blogs of mine to learn the smartest ways how you can achieve this in Excel.