+ Reply to Thread
Results 1 to 13 of 13

Returning non-blank cells across a range in Excel

  1. #1
    Registered User
    Join Date
    07-09-2018
    Location
    Bristol, England
    MS-Off Ver
    2010
    Posts
    2

    Returning non-blank cells across a range in Excel

    Hi there

    I have an excel sheet, simplified version below, with 7 columns each with one row containing data and the rest blank. How do I return the non-blank values from each row in the right hand column?

    2018-07-09 17_11_38-Return Non Blanks - Excel.png

    Thank you!

    Clare

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Returning non-blank cells across a range in Excel

    Maybe

    =lookup(1e+100,A2:G2)

    I can't see which rows or columns your sample is in, so had to guess.

  3. #3
    Forum Contributor Mvaldesi's Avatar
    Join Date
    01-21-2011
    Location
    Plano, TX
    MS-Off Ver
    MS365 (PC) v.2108
    Posts
    257

    Re: Returning non-blank cells across a range in Excel

    If you're certain all the blank cells are truly null (no spaces or anything,) you can just concatenate the cells in each row together leaving you with just the non-blank values.

    =CONCAT(A2,B2,C2,D2,E2,F2,G2)
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Returning non-blank cells across a range in Excel

    How about this?

    =SUM(A2:G2)

    (assuming there is just one number in each row).
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: Returning non-blank cells across a range in Excel

    Maybe as on the attached PrntScr.
    Regards.
    Attached Images Attached Images

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Returning non-blank cells across a range in Excel

    Quote Originally Posted by Mvaldesi View Post
    =CONCAT(A2,B2,C2,D2,E2,F2,G2)
    Bearing in mind the source data is numeric, this might not be such a good idea, try =SUM() on the results of your suggestion and the result will be 0.

    Quote Originally Posted by AliGW View Post
    =SUM(A2:G2)
    Sometimes it is all too easy to overlook something so obvious

    MIN() MAX() MEDIAN() or AVERAGE() would also work, I'm sure there must be others.
    Last edited by jason.b75; 07-09-2018 at 01:04 PM.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Returning non-blank cells across a range in Excel

    Well, maybe this:

    =IF(SUM(A2:G2)=0,"",SUM(A2:G2))

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Returning non-blank cells across a range in Excel

    Quote Originally Posted by maras_mak View Post
    Maybe as on the attached PrntScr.
    Regards.
    It would be helpful if you posted the formula into your message so that anyone wishing to use it can copy and paste.

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Returning non-blank cells across a range in Excel

    I think you might have misread my post, Ali, my first comment was directed at mvaldesi's suggestion, which would return the result as text.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Returning non-blank cells across a range in Excel

    Ah, yes, I think I did - not for the first time!!!

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Returning non-blank cells across a range in Excel

    try this
    Enter formula in H1 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C D E F G H
    1 1 1
    2 2 2
    3 3 3
    4 4 4
    5 5 5
    6 6 6
    7 7 7
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  12. #12
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: Returning non-blank cells across a range in Excel

    AliGN, You are right.
    Formula in 'M1'
    Please Login or Register  to view this content.
    Regards.

  13. #13
    Registered User
    Join Date
    07-09-2018
    Location
    Bristol, England
    MS-Off Ver
    2010
    Posts
    2

    Re: Returning non-blank cells across a range in Excel

    Thank you all so much for your solutions to this problem.

    In the end, the concatenation was the simplest way to avoid returning the blank cells and this has solved my problem - thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Checking a range for cells not in blank, then returning that value
    By Andreas_Zopo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-02-2016, 03:11 PM
  2. Count blank cells in a range and contiguous blank cells also as single cells! Tricky One!
    By SebastianColombia in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-31-2015, 02:32 PM
  3. [SOLVED] Blank cells not returning a blank value...
    By Losguapos1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-10-2014, 11:01 AM
  4. [SOLVED] Returning the last non-blank value in a horizontal range
    By AliGW in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-26-2014, 10:44 AM
  5. [SOLVED] Excel function is returning undesired answer for blank cells
    By dcwandj in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-28-2012, 12:14 PM
  6. Replies: 2
    Last Post: 06-21-2012, 04:51 PM
  7. Excel 2010 Pivot Table Returning Count of Blank Cells
    By snikrs11 in forum Excel General
    Replies: 0
    Last Post: 03-07-2011, 04:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1