Concatenate cells with 5 characters each column but ignore if blank

Hi All,

Good Morning! I'M In Need Of Help To Figure Out How To Concatenate Columns By A Dash With 5 Character Each Column But If The Column Is Empty The Formula Must Ignore Move On To The Next Column.


Columns A | B | C | D | E | F | G | H |

This Is How It Should Be Concatenated And Please Note The Length Value Inside The Column Should 5 So I Need Automatic Leading Zero If My Value Each Col Is Less Than 5:

COLUMN GROUPING: A-B | C-D | E-F | G-H

A | B | C | D | E | F | G | H |
BLK1 | BLK2 | BLK3 | | BLK5 | BLK6 | BLK7 | |

OUTPUT SHOULD BE: 0BLK1-0BLK2 0BLK3 0BLK5-0BLK6 0BLK7

Please Help!

Try this lazy formula

=IF(A1<>"",RIGHT("0000"&A1,5),"")&IF(B1<>"",IF(A1< >"","-","")&RIGHT("0000"&B1,5),"")&IF(C1<>"",IF(COUNTA(A 1:B1)<>0,"-","")&RIGHT("0000"&C1,5),"")&IF(D1<>"",IF(COUNTA(A 1:C1)<>0,"-","")&RIGHT("0000"&D1,5),"")&IF(E1<>"",IF(COUNTA(A 1:D1)<>0,"-","")&RIGHT("0000"&E1,5),"")&IF(F1<>"",IF(COUNTA(A 1:E1)<>0,"-","")&RIGHT("0000"&F1,5),"")&IF(G1<>"",IF(COUNTA(A 1:F1)<>0,"-","")&RIGHT("0000"&G1,5),"")&IF(H1<>"",IF(COUNTA(A 1:G1)<>0,"-","")&RIGHT("0000"&H1,5),"")

Hi

Another one

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT("0",5-LEN(A1))&A1&"-"&REPT("0",5-LEN(B1))&B1&" "&REPT("0",5-LEN(C1))&C1&"-"&REPT("0",5-LEN(D1))&D1&" "&REPT("0",5-LEN(E1))&E1&"-"&REPT("0",5-LEN(F1))&F1&" "&REPT("0",5-LEN(G1))&G1&"-"&REPT("0",5-LEN(H1))&H1&" ","-00000 "," "),"00000-",""),"00000 "," "))