When you import data from another application into Excel, you sometimes might have to deal with a dataset that contains unnecessary characters.
For example, some data values may have extra/unnecessary spaces. Sometimes, some text strings may contain random numbers.
Some special characters (e.g. @, #, $, etc.) may randomly be inserted.
Here’s a visual illustration as an example:
The above illustration has it all. Unwanted characters, random special characters, and unnecessary spaces.
We’re not going to be dealing with the above issues in this article though (you can read this article if you want to know how to remove specific characters from a data string).
Rather, in this article, we’ll be dealing with this kind of issue:
The problem here is that there’s a random unnecessary character for each value.
These values should only contain numeric values.
What’s more, all these unnecessary characters are the first characters of each data string.
Usually, when this happens, you’d want to delete these unnecessary characters.
Now while you’re free to do this manually, it’ll take time. And if you’re working with a large dataset, it’s just plain inefficient.
Fortunately, there are other several ways you can go with this.
And they are much more efficient than manually removing the first character in each cell.
In this article, we will be discussing these other ways you can remove the first character from a cell in Excel.
By the end of this article, you should be able to efficiently clean your data anytime you want or have to.
Let’s get started.
Use a Combination of RIGHT and LEN to Remove the First Character From a Value
For the first method, we’ll be using a combination of two functions: RIGHT and LEN.
What this combination does is it skips the first character of a value.
It then returns the rest of the characters of the value. It effectively removes the first character.
The formula for using this combination is as follows:
=RIGHT(value,LEN(value)-1)
Where
value - refers to the value or cell that contains the value from which you want to remove the first character
The RIGHT function returns the specified number of characters from a value or string starting from the right.
To make the process more efficient, rather than specifying the number of characters for each value or cell, we will use the LEN function.
The LEN function counts the total number of characters that the value has.
By subtracting the 1 from it, we always get one less character from the value (starting from the right).
Thus, by using this combination, we can remove the first character from any value or string.
How to Remove the First Character From a Value
Let’s use this dataset for illustration:
What we want to do here is remove the first character from each cell (except the column header).
To do so, we’ll be using the combination of the RIGHT and LEN functions.
- Select an empty cell. Ideally, this cell would be adjacent to the cell from which we’ll be removing the first character. In our illustration, this will be cell B2.
- In the selected cell, enter the formula for the combination of the RIGHT and LEN functions. In our illustration, this formula will be =RIGHT(A2),LEN(A2)-1).
- Press the ENTER key on your keyboard. The formula should now return a value that doesn’t include the first character.
- Copy-paste the formula to the rest of the column (until the next empty row). We have successfully removed the first character from each cell. (I have formatted the cells to make them more presentable).
Things to Note
- If you want to remove more characters from the beginning of the value or string, you only need to change 1 to the number of characters you want to remove. For example, if you want to remove the first two (2) characters from cell A2, the formula will then be =RIGHT(A2),LEN(A2)-2).
- The result of the formula will always be treated as text by Excel even if contains numerical values only. If you want the result to be treated as a number, add *1 to the end of the formula (e.g. =RIGHT(A2),LEN(A2)-1)*1).
Use a Combination of MID and LEN to Remove the First Character From a Value
For the next method, we’ll be using a combination of the MID and LEN functions.
It does a similar thing to our previous method. Ultimately, it allows us to remove the first character from a value or string.
The formula for using this combination is as follows:
=MID(value,2,LEN(value)-1)
Where
value - refers to the value or cell that contains the value from which you want to remove the first character
The 2 in the formula refers to the starting position of the character. We put 2 here because we only want to return the specified number of characters of the value or string starting from the 2nd character.
Which means it skips the first character. The LEN function counts the total number of characters that the value has.
By subtracting the 1 from it, we always get one less character from the value
How to Remove the First Character From a Value
Let’s use this dataset for illustration:
What we want to do here is remove the first character from each cell (except the column header).
To do so, we’ll be using the combination of the MID and LEN functions.
- Select an empty cell. Ideally, this cell would be adjacent to the cell from which we’ll be removing the first character. In our illustration, this will be cell B2.
- In the selected cell, enter the formula for the combination of the MID and LEN functions. In our illustration, this formula will be =MID(A2),2,LEN(A2)-1).
- Press the ENTER key on your keyboard. The formula should now return a value that doesn’t include the first character.
- Copy-paste the formula to the rest of the column (until the next empty row). We have successfully removed the first character from each cell. (I have formatted the cells to make them more presentable).
Things to Note
- The result of the formula will always be treated as text by Excel even if contains numerical values only. If you want the result to be treated as a number, add *1 to the end of the formula (e.g. =MID(A2),2,LEN(A2)-1)*1).
Use REPLACE to Remove the First Character from a Value
For the next method, we’ll be using the REPLACE function. Unlike the first two methods which remove the first character by skipping it, the REPLACE function removes the first character by replacing it with a blank.
The formula for using the REPLACE function is as follows:
=REPLACE(value,start_num,num_chars,new_value)
Where
value - refers to the value or cell that contains the value from which you want to remove the first character
start_num – refers to the starting position of the character or characters that you want to replace
num_chars – refers to the number of characters that you want to replace
new_value – the value that will replace the character/characters specified by start_num and num_chars
So to remove the first character of a value, the formula will then be:
=REPLACE(value,1,1,"")
We set both start_num and num_char to 1 because we only want to remove the first character.
We set new_value to "" because we want to replace the first character with a blank.
How to Remove the First Character From a Value
Let’s use this dataset for illustration:
What we want to do here is remove the first character from each cell (except the column header).
To do so, we’ll be using REPLACE function.
- Select an empty cell. Ideally, this cell would be adjacent to the cell from which we’ll be removing the first character. In our illustration, this will be cell B2.
- In the selected cell, enter the formula for the REPLACE function. In our illustration, this formula will be =REPLACE(A2,1,1,"").
- Press the ENTER key on your keyboard. The formula should now return a value that doesn’t include the first character.
- Copy-paste the formula to the rest of the column (until the next empty row). We have successfully removed the first character from each cell. (I have formatted the cells to make them more presentable).
Things to Note
- The result of the formula will always be treated as text by Excel even if contains numerical values only. If you want the result to be treated as a number, add *1 to the end of the formula (e.g =REPLACE(A2,1,1,"")*1).
- If you want to remove more characters from the beginning of the value or string, you only need to change num_chars to the number of characters you want to remove. For example, if you want to remove the first two (2) characters from cell A2, the formula will then be =REPLACE(A2,1,2,"").
Apply Flash Fill to Remove the First Character From a Value
For the next method, we’ll be using Excel’s Flash Fill via a keyboard shortcut.
What Flash Fill does is it automatically enters values based on a pattern.
We’ll be using this characteristic to remove the first character from each cell in a group of cells (in the same column). Here’s how to do so.
How to Remove the First Character From a Value
Let’s use this dataset for illustration:
- Make sure that the column next to this group of cells is empty. Next, select the cell that is adjacent to the first cell from which we want to remove the first character. For our illustration, this will be cell B2.
- In the selected, enter the value of the cell but skip the first character. For our illustration, we will enter 3171.
- Press the ENTER key on your keyboard. Then, press the keyboard shortcut Ctrl + E. This will apply Flash Fill to the rest of the column (until the next empty row).
- You have successfully removed the first character from each cell.
Things to Note
- The pattern here is entering the value but skipping the first character.
- This method is not dynamic. You’ll have to redo it if there are changes to the reference values or cells.
- The results will come out as values.
Create a Custom Function to Remove the First Character From a Value
If you don’t mind doing a little bit of coding, then this method might work for you.
The code that you’ll be needing is as follows:
The `RemoveFirstChar` function takes a string (`str`) and a numeric value (`num_chars` As Long) as inputs, and removes the specified number of characters from the beginning of the string.
RemoveFirstChar = Right(str, Len(str) - num_chars) End Function
Copy this code as you’ll be needing it later.
How to Create a Custom Function
- Open the Developer Tab. Then, click the Visual Basic button.
- In the VBA window, open the Insert tab. Then, click on Module.
- In the Module window, paste the code that you copied from above.
- Close the VBA window
How to Remove the First Character From a Value
Let’s use this dataset for illustration:
What we want to do here is remove the first character from each cell (except the column header). To do so, we’ll be using our newly created custom function.
- Select an empty cell. Ideally, this cell would be adjacent to the cell from which we’ll be removing the first character. In our illustration, this will be cell B2.
- In the selected cell, enter the formula for our custom function. In our illustration, this formula will be =RemoveFirstChars(A2,1).
- Press the ENTER key on your keyboard. The formula should now return a value that doesn’t include the first character.
- Copy-paste the formula to the rest of the column (until the next empty row). We have successfully removed the first character from each cell. (I have formatted the cells to make them more presentable).
Things to Note
- If you want to remove more characters from the beginning of the value or string, you only need to change 1 to the number of characters you want to remove. For example, if you want to remove the first two (2) characters from cell A2, the formula will then be =RemoveFirstChars(A2,2).
Conclusion
And those are the different methods you can follow to remove the first character of a value or string. I hope that you'll be able to use your learnings here in your future endeavors.
Spotted an outdated link or a mistake? Email editors@fundsnetservices.com and we will review it.