Concatenate & remove blanks

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a worksheet with some check boxes in it. If the check box is checked, I want another value to show up somewhere else on the worksheet. I got this to work. However, now I have a bunch of random cells with values and some without. I want to take these cells in the range (A1:A27) and concatenate them so they are consolidated into a single cell. I also need to take out any of the blanks that I am given in this range due to not checking the check box. Any thoughts? Thanks in advance!
 
I think I may have figured out the below now, but now I want the concatenated data to line up top to bottom in a single cell. So instead of looking like this:

Apple Orange Banana

It would look like this:

Apple
Orange
Banana

Any thoughts?

----- Jaime Andersen wrote: -----

I have a worksheet with some check boxes in it. If the check box is checked, I want another value to show up somewhere else on the worksheet. I got this to work. However, now I have a bunch of random cells with values and some without. I want to take these cells in the range (A1:A27) and concatenate them so they are consolidated into a single cell. I also need to take out any of the blanks that I am given in this range due to not checking the check box. Any thoughts? Thanks in advance!
 
I got the concatenate to work, but it looks like this in a single cell:

Apples Oranges Bananas Grapefruit

I want the cell to display like this:

Apples
Oranges
Bananas
Grapefruit

Some of the entries are longer than others so I cannot just use wrap text and try to fit it that way. Is there a way to have an <Enter> within a cell so that the appearance would be as above?

----- alcorjr wrote: -----

Please clarify;)
 
Jaime,

I'm assuming your concatenating using
=CONCATENATE(A1, " ", B1, " ", etc...) to get spaces between
replace the " " with CHAR(10) and make sure the cells are
formatted to wrap text.

Dan E
 
The CHAR(10) technique works great, but what if I have a bunch of blanks?... It just creates a cell that it 10 rows high! Is there a way to suppress the CHAR(10) when there are blanks?

----- Dan E wrote: -----

Jaime,

I'm assuming your concatenating using
=CONCATENATE(A1, " ", B1, " ", etc...) to get spaces between
replace the " " with CHAR(10) and make sure the cells are
formatted to wrap text.

Dan E
 
You could check to see first:

=IF(A1="","",A1&CHAR(10)) & IF(A2="","",A2&CHAR(10)) & IF(A3="","",A3&CHAR(10))

(maybe leave the last &char(10) off if you don't like to finish with the
alt-enter.)
 
Back
Top