Concatenate w/exceptions
Posted by Abbie on September 21, 2001 10:24 AM
I have 4 columns of data which I have used the concatenate function to merge into one cell. There are several instances where one of the cells correctly has no data. Is it possible to write a formula that will only concatenate cells that have data in them? And if so, how?
Posted by Mark W. on September 21, 2001 10:42 AM
Abbie, I'm somewhat puzzled by what you call "no
data". If cell A1 contains "dog", B1 is empty,
and C1 contains "wood" then =A1&B1&C1 or
=CONCATENATE(A1,B1,C1) would produce "dogwood".
In effect, I've concatenated only cells that
contain data. What exactly do you mean?
Posted by Abbie on September 21, 2001 12:40 PM
Mark, no wonder you are confused. Let me try again, I didn't give you near enough information the first time I do apologize.
The current formula looks somewhat as follows: The 2 spaces were put in the formula to provide a break at the end of the data reported.
=CONCATENATE(A1," ",B1," ",C1, )
If A1 contains "Dog", B1 is empty, C1 contains "Wood" and the result is Dog Wood.
I want the result to appear as Dog Wood.
I'm trying to find a way to ignore the extra spaces I've added if there is no data in the cell.
In the actual worksheet, there are 15 columns and 800 rows. Each column represents a person and each row is an answer to a specific question. Not every person was required to answer every question. I used the additional 2 spaces to separate the data in each cell. My problem occurs when there is only data in A1, G1, and N1.
Then the result shows 2 spaces for each empty cell between cells with data.
Maybe this explanation will be less confusing and more informative. Thanks again for your help.
Abbie~~
Posted by Barrie Davidson on September 21, 2001 1:10 PM
The current formula looks somewhat as follows: The 2 spaces were put in the formula to provide a break at the end of the data reported. =CONCATENATE(A1," ",B1," ",C1, ) If A1 contains "Dog", B1 is empty, C1 contains "Wood" and the result is Dog Wood. I want the result to appear as Dog Wood. I'm trying to find a way to ignore the extra spaces I've added if there is no data in the cell. In the actual worksheet, there are 15 columns and 800 rows. Each column represents a person and each row is an answer to a specific question. Not every person was required to answer every question. I used the additional 2 spaces to separate the data in each cell. My problem occurs when there is only data in A1, G1, and N1.
Abbie, would the TRIM function work for you? To use your example formula:
=TRIM(A1&" "&B1&" "&C1)
The TRIM function will remove all spaces from text except for single spaces between words.
Regards,
BarrieBarrie Davidson
Posted by Abbie on September 21, 2001 1:37 PM
Perfect, Barry, Thank you!!
