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: 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:
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.
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.
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: 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: 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: Here are the steps to use this code from the immediate window: 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 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
