# Checking Whether Cells in a Range are Blank, and returning values if it isn't

#### dhman08

##### New Member
New to the excel world and I'm baffled on how to fix this problem. Any help will be much appreciated. This is what I'm trying to do - I have a range of data which includes blank and non-blank cells. What I'm trying to do is a) For each row check to see if the cell is blank. This check will be done for every other cell starting with column A and b) if it is, I don't want anything returned, but if it isn't I'd like the value to be returned.

Ideally, what I'd like to do is perform all these checks in one cell and the value to the returned in the same cell at the end of the row. So for example, if I look at row 1, column A, E, I have values, but column C and G have no values. So I'd like the formula to essentially skip column C and E and add A, E, and I and return them as a text string in Column K.

 Column A B C D E F G H I J K L Vendor PRICE Vendor PRICE Vendor PRICE Vendor PRICE Vendor PRICE Total Vendor Total PRICE BCEI 1.1 AAPL 5.2 SPY 3.3 TCON 2.2 UTX 1.09 VNR 3.2 MSFT 4.5 SPYG 1.05 VTR 1.56 AMGN 3.2

<tbody>
</tbody>

Last edited:

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### AliGW

##### Banned
What would your expected outcomes be in column K for the sample data? Why does it need to be text and not a number?

Is there a reason why you cannot just use SUM? e.g. =SUM(A2:J2)

Last edited:

#### dhman08

##### New Member
Looking at the table that I have for Row 1, expected outcome would be BCEIAAPLSPY. Column K deals strictly with entity names. Hence the text, not a number.

I believe sum will return a 0 value, not the expected value I return

Last edited:

=A2&C2&E2&G2&I2

#### dhman08

##### New Member
That'll only work for my sample data now, but if I have a massive spreadsheet containing 30000 row it won't be efficient.

#### AliGW

##### Banned
Why not? It's just a case of copying the formula down. Or are you saying that your sample data is not properly representative of the real data?

#### hiker95

##### Well-known Member
dhman08,

Excel 2007
ABCDEFGHIJKL
1VendorPRICEVendorPRICEVendorPRICEVendorPRICEVendorPRICETotal VendorTotal PRICE
2BCEI1.1AAPL5.2SPY3.3BCEIAAPLSPY9.60
3TCON2.2UTX1.09TCONUTX3.29
4VNR3.2MSFT4.5VNRMSFT7.70
5SPYG1.05VTR1.56AMGN3.2SPYGVTRAMGN5.81
6
Sheet1
Cell Formulas
RangeFormula
K2=A2&C2&E2&G2&I2
L2=B2+D2+F2+H2+J2

The formula in cell K2, copied down:

=A2&C2&E2&G2&I2

The formula in cell L2, copied down:

=B2+D2+F2+H2+J2

#### dhman08

##### New Member
That is a perfect solution for now, but if I have 30000 rows and 5000 columns that will not be a efficient way to do this. So I'm trying to see if there are better solution than that. Thank you for your suggestion though

#### hiker95

##### Well-known Member
That is a perfect solution for now

dhman08,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

#### AliGW

##### Banned
So, are you saying that you are looking for an automated solution? If so, you probably need to add VBA to your thread title to attract the right helpers. I'm afraid VBA is not my speciality.

Replies
1
Views
227
Replies
2
Views
171
Replies
2
Views
214
Replies
9
Views
202
Replies
20
Views
616

1,190,746
Messages
5,982,717
Members
439,791
Latest member
NwaTech_

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back