Find the value in the first non blank cell in Microsoft Excel

In this article we will learn how to find the value in the first non-blank cell in a range, we will use  Index, Match and Len formulas in Microsoft Excel.

To find the value in first non blank cell we use Index function to extract the value from a range, Match function will help to match the criteria and Len function will help in Match function to give the criteria.

Let’s take an example to understand how to find the value in the first non-blank cell in range.

Example 1: We have a list in column A, and some cells are blank. We need to put the formula in column B to find the value in the first non-blank-cell in the range.

• Select the Cell B2; write the formula
• =INDEX(A2:A10,MATCH(TRUE,LEN(A2:A10)<>0,0),1)
• This is an array formula so you have to press the keys “CTRL+Shift+Enter” together on the keyboard.
• The function will return the text "Fruit" in cell B2.
• To return the value for the rest of cells, copy same formula.
• To copy formula in range B3:B10, copy formula by pressing the key “CTRL+C” and paste in the range B3:B10 by pressing the key “CTRL+V”.

To remove the formula from the data use the “Paste Special” option:-

• Select the range B2:B10, copy by pressing the key “CTRL + C”.
• Right click on the mouse to select “Paste Special”.
• In the dialog box select value and click on OK.

The formula will be removed and the cells will contain values.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com