Concatenate formula including only cells that contain value

[giginho]

Hi everyone, I am trying to use excel to store my patients findings, and to create a report. A sort of very cheap and limited electronic health record.

To do that I am using a table with multiple drop lists to input the findings, and the "concatenate" function to create the report. I would like to include in the concatenate formula only the cells that have a value, and ignore the ones that have no finding.

For example:
example.jpg


The formula would be: =CONCATENATE("Inspection revealed ",A2," of the head, ",B2," of the neck, ",C2," of the knees, ",D2," of the feet.")

Result: Inspection revealed anterior head carriage of the head, hyperlordosis of the neck, of the knees, increased Fick's angle of the feet.

In this case I would like to ignore the "knees" part of the formula. I hope it makes any sense. Any suggestions?

Thanks

[Glenn Kennedy]

So, what do you want in the case of knees - any reference to kness left out entirely, or what?

[giginho]

Thanks for the immadiate response!

It would be nice to be able to input something like for example "knees valgus angle appears within normal limits" or even ignore it completely.

[ibt]

Here is a solution (also attached), although not a very elegant one, using the formula:

="Inspection revealed " & IF(B1<>"",B1 &" of the " & LOWER(A1) &", ", "") & IF(B2<>"",B2 &" of the " & LOWER(A2) &", ", "")& IF(B3<>"",B3 &" of the " & LOWER(A3) &", ", "")& IF(B4<>"", "and " & B4 &" of the " & LOWER(A4) &".", "")

which gives the output:

Inspection revealed anterior translation of the head, hyperlordosis of the neck, and increased Fick's angle of the feet.

The last statement of the formula has been slightly altered so it includes the word "and" and has a period at the end instead of a comma.

Let me know if that works for you, if not I could try a different approach.

[Glenn Kennedy]

ibt... if you think your effort was inelegant, I won't embarass myself by posting the multi-line monster that I have in front of me.

[giginho]

Thank you ibt, it works perfectly for the example but I think it wouldn't be very easy for me to apply your solution to the larger tables that I am using. I am a real newbie.

Can I use any IF function inside the concatenate formula? For example, if blank then use the content of another cell, or if blank, ignore it.

[Glenn Kennedy]

That was the way I was going, but it was truly horrible. To be honest, I think you'd be better served by a VBA solution (as Excel's concatenate functionality is a tad limited). However, I'm a VBA newbie, so I'm out...

[sourabhg98]

However, I'm a VBA newbie, so I'm out...

Really??

However try my solution....

[giginho]

Thank you sourahg98 but I finally found a solution with the input of ibt.

This following formula seems to work and is exactly what I was looking for..

=CONCATENATE("Inspection revealed "&IF(A1<>"",A1&" of the head, ","") &IF(B2<>"",B2&" of the cervical spine, ",""))

Thanks ibt!

[ibt]

Thanks, I noticed there is an error in my solution in the case that the last body part does not have an issue. But here it is in VBA (I am on the newer side using it).

The code is:

Please Login or Register  to view this content.

Let me know if you have issues.