Concatenate works, but I need to omit descriptive text when cell is empty

[michigandrea]

Hi all,

I have a worksheet with elements from a journal citation in each row that I need to combine in one cell. Concatenate works beautifully for most of the data and even allows me to put the proper separators and descriptive text between the cells where needed. However, there is a column that is only populated in some (very few) of the records. This value needs to be preceded by the word suppl when it appears, but I don't want suppl to appear if the cell is empty.

I've tried messing with substitute, trim, if and other formulas but I'm not quite getting it. Any tips?

The worksheet is essentially:

A	B	C	D	E	F	G	H
APR	2013	80	15			1378	1384
JUL	2010	54	1			58	65
JAN	2011	9	4		5	445	451

I need to resulting concatenated cells to look like this:

2013 APR; 80(15): 1378-1384
2013 JAN; 54(1): 58-65
2013 JUL; 9(4): suppl 5 445-451

With the "suppl" only appearing when there is a value in the F column. Can this be done?

THANK YOU!!!

[Olly]

Use an IF statement for column F - something like:

Please Login or Register  to view this content.

[ChemistB]

Add an IF statement inside the concatenate. For example

...& IF(LEN(F2>0), "Suppl "&F2,"") & ...
Make sense?

[michigandrea]

Beautiful! You all are the best! I thought there would be something elegantly simple, and sure enough! Thank you very, very much!