# Concatenate non-blank cells

1. ## Concatenate non-blank cells

Hi, I've tried a few different solutions to this one, but not quite there.

I have 3 cells that I need to concatenate with a comma, but only want to include them if they are not blank. Any one (or two) could be blank, and there could be two word entries.

The closest I have is:
Flat, A, 27, Bailey, St

Which I need to come out as:
Flat A, 27, Bailey St

using the formula:
=SUBSTITUTE(TRIM(BV6 & " " & BW6 & " " & BX6), " ", ", ")

Can this be done without a UDF? What if there are more than 3 cells?

2. ## Re: Concatenate non-blank cells

Try using * (or some other character that won't be found in your data) to substitute for.

Like this:
=SUBSTITUTE(TRIM(BV6 & "*" & BW6 & "*" & BX6), "*", ", ")

3. ## Re: Concatenate non-blank cells

Nice one, and to omit any cells that are blank at the start of the range, would you use IF?

E.g. I get , , Bailey St if there is no 'Flat A' and '27':

=IF(BV6&BW6="",BX6,IF(BV6="",SUBSTITUTE(TRIM(BW6 & "*" & BX6), "*", ", "),SUBSTITUTE(TRIM(BV6 & "*" & BW6 & "*" & BX6), "*", ", ")))

Edit2: This still didn't quite work, so found a UDF to use:

``Please Login or Register  to view this content.``
=ConCat(", ",A3:A9,C5,"I'm text")

4. ## Re: Concatenate non-blank cells

If you still want (or prefer) a formula solution, try:

=SUBSTITUTE(IF(BV6="","",BV6&", ")&IF(BW6="","",BW6&", ")&IF(BX6="","",BX6&", "),", ","",COUNTIF(BV6:BX6,"?*"))

or

=SUBSTITUTE(BV6&REPT(", ",BV6<>"")&BW6&REPT(", ",BW6<>"")&BX6&REPT(", ",BX6<>""),", ","",COUNTIF(BV6:BX6,"?*"))

5. ## Re: Concatenate non-blank cells

Many thanks for your help - I'll give this a go.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1