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!

 

[ghiegomez08]

Almost there! when column 12 is blank it still have the dash... I tried to remove it but it says too many arguments.

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

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[Rick Rothstein]

Almost there! when column 12 is blank it still have the dash... I tried to remove it but it says too many arguments.

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

<TBODY>
</TBODY>

A small addition (highlighted in red) to the formula I posted takes care of that...

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

 

[ghiegomez08]

You are awesome, Rick! worked like a charm! Thank you soooooo much for your patience and your help.... really really appreciate it!!!

 

[ghiegomez08]

Just one quick question... why did you use substitute twice? I wanted to understand the formula for future use Thanks!

 

[Rick Rothstein]

Just one quick question... why did you use substitute twice? I wanted to understand the formula for future use Thanks!

With any cell (except the first) possibly missing, there are two possibilities involving a dash... either there is an isolated space/dash or an isolated dash/space. I simply replaced either of them with a space and then let TRIM remove an doubled (tripled, etc.) internal spaces that may have resulted. The only thing I had forgotten originally was if the last item was missing but the next to last item was not missing... that resulted in a dash at the end with no space following it, so my two substitutions missed it. As you saw, the fix was easy, concatenate a space onto the end of the text so there would be a dash/space at the end (assuming the last item was missing, but the next to last wasn't) for one of those substitutions to find.

 

[ghiegomez08]

ah got it! thanks again!

 

[ArtTurning]

Rick - you're a wizard ! This solved a very similar problem for me, although nearly 10 months later ! Thanks so much.

 

[Fazila]

Hello

I have a very similar problem I'm trying to resolve. I need to join a number of cells together so for this example say I have to join column AV, BE and BH together with a / between each cell reference, however, not all cells have a value i.e.

row 1:

AV BE BH
SRA, 1029 SLN, 5010

The answer I need is SRA, 1029 3pm to 4pm/SLN, 5010 4pm to 5pm

Is there any way this can be done?

Thanks