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 🧪
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”.
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).
Step 5) Go to the Home Tab > Editing Group > Fill button > Flash Fill.
Or use the keyboard shortcut of the Ctrl key + E.
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 📏
Alternatively, after you have written the text string by removing the first character in the first cell as below.
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.
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 🤯
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.
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.
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.
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.
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).
Step 6) That’s it. Press Enter.
Step 7) Drag this formula down the list.
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.
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.
Step 3) Nest the above function in the RIGHT function through the following formula:
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.
Step 4) Drag this formula down the list.
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:
Similarly, the RIGHT and LEN functions become:
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️⃣
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:
If I use the REPLACE function to remove the first numeric character from each of these numbers:
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.
Values formatted as text are left-aligned, whereas, numbers are right-aligned.
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:
Step 2) Nest the REPLACE function into it.
Step 3) Or, nest the RIGHT & LEN function as below:
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.