Hello Excellers and welcome back to another blog post in my series of #FormulaFiday #ExcelTips. Today, let’s answer an Excel question from one of my subscribers. They want to be able to delete the first two digits of a string in a column of data. No other instances instances of the digits in other parts of the cells are to be deleted. With about 5000 rows of data, only the first two digits of the string which are ’98’ need to be removed. No other instances of these digits should be deleted from the data set. Here is a hint how to solve this. We will use both the RIGHT function and the LEN function.
So, we cannot use the Find and Replace process as this would replace all of the instances of 98 in the cells. A data sample is below. We will be using the RIGHT function and the LEN function in this solution.
Using Right and LEN Functions.
So, as already stated, the solution to this problem is to use both the RIGHT and the LEN Functions in Excel. The first part of the solution is to use the RIGHT function. The purpose of this function is to extract text from the right of a string. This sounds like this would be useful for this solution. The syntax of the RIGHT function as a quick recap is:
=RIGHT (text, [num_chars])
Arguments are as follows-
text – The text from which to extract characters on the right.
num_chars – [optional] The number of characters to extract, starting on the right. Optional, default = 1.
So, let’s now move onto the second function we are using in this solution. THe LEN function, which returns the length of a given text string as the number of characters. LEN counts characters in numbers, but number formatting is not included. The syntax of the LEN function as another quick recap is:
=LEN (text)
Arguments are as follows –
text – The text for which to calculate length.
Writing The Excel Formula To Delete The First Two Digits In Excel.
Finally, it is time to bring all of this together and delete the first two digits of our cells. As already discussed, we cannot use Find and Replace as it would replace all instances of 98 in the cells. So, let’s build the formula and walk it through.
The RIGHT function takes the string in A2, this is the text to extract the characters from the right. The number of characters that need to be extracted (the second argument in the RIGHT function), is calculated by the LEN function. This is calculated as 7 minus 2 which leaves us with the five digits from the right.
So, how cool is that?. This leaves any other instances of the 98 combination in place. A simple but clever way to delete the first two digits in my Excel cells.
