Hello Excellers and welcome back to another blog post in my 2019 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. An extract of the type of data set can be seen 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])
- 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 will also count characters in numbers, but number formatting is not included. The syntax of the LEN function as another quick recap is:
- text – The text for which to calculate length.
Writing The Excel Formula.
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.
What Next? Want More Tips?
So, if you want more top tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Do You Need Help With An Excel Problem?.
I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.