Concatenate Cells with dashes/spaces and ignore blank cells

[ghiegomez08]

Hi guys!

I checked out and/or searched all the possible formulas that I can use for my project below but no luck so I have finally decided to ask this question...

So, my spreadsheet has 12 columns that I need to concatenate using a space and dash.however, the concatenate leaves me with "-" (dash) if the cell is blank...

(| = column)
I have color coded the columns that would have the dash and spaces in between to separate the range.

CACONT:PLRU | D | 000074 | SEG | 0ACCT | NUMBR | 00001 | 00005 | BLOCK | | 0001 | 0004

my goal is to have this result...
CACONT:PLRU D-000074 SEG 0ACCT-NUMBR 00001-00005 BLOCK 0001-0004

Additional example:

CACONT:PLRU | D | 000074 | SEG | 0ACCT | NUMBR | 00001 | | BLOCK | | 0001 | 0004

result:
CACONT:PLRU D-000074 SEG 0ACCT-NUMBR 00001 BLOCK 0001-0004


thanks in advance for your help!

 

[Rick Rothstein]

Perhaps you can make use of the UDF (user defined function) I posted here (it has an option to handle that problem)...

Flexible Concatenation Function

 

[AnAnalyst]

... or you could use a formula something like this:

=IF(A1="","",A1)&IF(B1="",""," "&B1)&IF(C1="","","-"&C1)&IF(D1="",""," "&D1)&IF(E1="",""," "&E1)&IF(F1="","","-"&F1)&IF(G1="",""," "&G1)&IF(H1="","","-"&H1)&IF(I1="",""," "&I1)&IF(J1="",""," "&J1)&IF(K1="",""," "&K1)&IF(L1="","","-"&L1)

 

[ghiegomez08]

Thank you Rick but I was hoping for an easy formula that I could plug in since i'm not familiar with VBA...

AnAnalyst: Thank you but I used this formula but when the first value in a range is blank it still leaves the dashes and it gets concatenated with another value. instead of just space... See below.

CACONT:PLRU | D | 000074 | SEG | 0ACCT | NUMBR | 00001 | 00005 | BLOCK | | | 0004
Result: CACONT:PLRU D-00074 SEG 0ACCT-NUMBR 00001-00005 BLOCK-0004
Should be like this: CACONT:PLRU D-00074 SEG 0ACCT-NUMBR 00001-00005 BLOCK 0004

Thanks!

 

[Rick Rothstein]

AnAnalyst: Thank you but I used this formula but when the first value in a range is blank it still leaves the dashes and it gets concatenated with another value. instead of just space... See below.

CACONT:PLRU | D | 000074 | SEG | 0ACCT | NUMBR | 00001 | 00005 | BLOCK | | | 0004
Result: CACONT:PLRU D-00074 SEG 0ACCT-NUMBR 00001-00005 BLOCK-0004
Should be like this: CACONT:PLRU D-00074 SEG 0ACCT-NUMBR 00001-00005 BLOCK 0004

Can you post the result for when all 12 columns have data in them (use A1 through A12 to make it easier for us to count) so we can tell where the dashes and spaces are supposed to be located at?

Also, so we are talking about the same thing, which columns are those 12 columns you are concatenating?

 

[ghiegomez08]

sure and I apologize for the confusion... Here's another example

COL: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12
CACONT:PLRU | D | 000074 | SEG | 0ACCT | NUMBR | 00001 | 00005 | BLOCK |0000A | 1900 | 1982

result: CACONT:PLRU D-000074 SEG 0ACCT-NUMBR 00001-00005 BLOCK-0000A 1900-1982

 

[ghiegomez08]

COL: 1 | 2-3 | 4 | 5-6 | 7-8 | 9-10 | 11-12

Columns 1 and 4 are constant value.

 

[Rick Rothstein]

sure and I apologize for the confusion... Here's another example

COL: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12
CACONT:PLRU | D | 000074 | SEG | 0ACCT | NUMBR | 00001 | 00005 | BLOCK |0000A | 1900 | 1982

result: CACONT:PLRU D-000074 SEG 0ACCT-NUMBR 00001-00005 BLOCK-0000A 1900-1982

Okay, got that. One more follow up question...

Can any of the cells be blank? Or are some cells guaranteed to always have something in them? If the latter, which cells would be guaranteed to always have data in them?

 

[ghiegomez08]

The only cell is that is guaranteed to have something is column 1 and the rest of the cells can be blank/empty...

 

[Rick Rothstein]

The only cell is that is guaranteed to have something is column 1 and the rest of the cells can be blank/empty...

You will have to test this carefully, but I think this formula may do what you want...

=TRIM(SUBSTITUTE(SUBSTITUTE(A1&" "&B1&"-"&C1&" "&D1&" "&E1&"-"&F1&" "&G1&"-"&H1&" "&I1&"-"&J1&" "&K1&"-"&L1," -"," "),"- "," "))