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.

• LEN Function In Excel
• Formula Friday- How To Use LEN in Excel To Highlight Issues With My Data Import
• Formula Friday – Add Leading Zeros To Numbers In Excel Using The TEXT Function
• Right Function
• A Formula To Check If You Have Extra Spaces In Your Data – Formula Friday

Hello Excellers. Welcome to another blog post article in my #Excel series. When you create a formula in Excel, you may expect to see the result of that formula immediately show up in the cell where you typed it in. But sometimes, instead of seeing the result, you see the actual formula displayed in the cell. So what’s going on, and how can you fix it? In this blog post, we’ll look at why formulas appear in cells instead of the results and how to fix them. Stay tuned! You can also watch the video to accompany this article. Why not subscribe to my YouTube channel and get immediate access to all of my playlists.

So, you may have already come across this issue in Excel. If you have not, you probably will at some point. Yes, the mysterious case of Excel shows the formula not the result. Your Excel worksheet cell will look something like this below.

Let’s work through the possible solutions to this problem and the reasons Excel is behaving like this.

Reason 1. Show Formulas Option Is Enabled.

The first reason Excel shows formulas, not results, is that you have Show Formula selected. Yes, that is correct. Have you accidentally set the Show Formula Option? It may be the reason Excel is showing formulas rather than the result of the formulas. You may have accidentally hit the Excel shortcut CTRL+’. To return this to normal view try hotting CTRL+’ again. Show formulas are available on the Formula Tab | Formula Auditing | Show Formulas.

Reason 2. Something In Front Of The Equals Sign.

The second reason Excel maybe shows formulas instead of results is that there is a space or some other symbol before the equals sign. If this is the case, then Excel will treat the formula as text and not evaluate it as a formula. So, Excel displays the actual cell contents.

Reason 3. Cells Are Formatted As Text.

So, if the formula looks good and you have checked for reasons one and two, there is another reason. Check that your cells’ formatting is not set as text. If your cell formatting is set to text then Excel will treat the formula as a text string. So, instead of evaluating the formula and generating the result. A text string of the formula is displayed. This however is a really easy fix, select the cell and reset the formatting. First, set the format to General, try changing the cell mode to edit (click in the formula bar or use F2, then enter. Excel will recognise the formatting change, resolving the text formatting issue

We hope this article was helpful in explaining why your Excel formula is not displaying the result you expect. If you have any further questions, please don’t hesitate to leave a comment and ask questions.

Need An Excel Problem Solved?. Try Excel Rescue.

Dont forget to sign up to the Excel at Excel Newsletter for THREE FREE Excel tips every month. Just click on the Sign Up Form to the right or use the link below.

• Formula Friday- My formula is not working- top reasons your formula is showing in Excel not the results.
• Find Your Cells that Contain Formulas- Quickly
• Excel Pivot Table Tip – Show Only The Top 10 Items In A Pivot Table
• Formula Friday – Need To Test Your Formulas? It’s Easy Just Hit F9!
• Show Zero Values In A Pivot Table

Happy Friday Excellers. Today I want to share with you how to RANK a data set with the rank formula in Excel, but ignore zero values. I use RANK a LOT, and sometimes you just do not want to include zero values in your results. When you are working with large data sets in Excel, sorting and ranking your data can be a helpful way to narrow down your focus. However, when you have zero values in your data set, it can be tricky to determine where your data should be ranked. In this blog post, we will show you how to use the RANK function in Excel to ignore zeros and rank your data accordingly. Let’s get started!

Rank Excel Formula Ignoring Zeros

We actually are going to make use of the IF function as well as RANK Excel function in this solution.  Here is sample data set I want to rank.

I have 10 lines of data and 2 of them are zero. I do not want zeros to be included in the ranking values or calculations.  This is the formula I am using to achieve this.

=IF(B4=0,””,RANK(B4,$B$4:$B$13,0))

Let’s Walk Through The Formula.

If B4 contains a zero then Excel returns a blank cell “”.  If the value in in cell B4 is not equal to zero then the value is ranked in the cell range of B4 to B13. This returns the ranking of 2.  We can then drag the formula down the length of the data range to populate the whole data set.

I hope you found this tip on how to use the Rank Excel Formula. See you next time for more #FormulaFriday fun.

Dont forget to sign up to the Excel at Excel Newsletter for 3 free Excel tips . I send out these tips every month. Just click on the Sign Up Form to the right or use the link below.

• Add A Rank Number Field To Your Pivot Table
• Formula Friday – Edit Multiple Formulas In Excel – FAST
• Formula Friday – Excel Rank Function – Who is The Leader Of The Pack?
• Formula Friday – Find The Minimum Value In A Range But Ignoring 0 Values
• An Excel VBA Macro To Protect All Cells With Formulas