Results 1 to 3 of 3

Thread: Concatenate cells with 5 characters each column but ignore if blank

  1. #1
    Junior Member
    Join Date
    May 2013
    Posts
    1
    Rep Power
    0

    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!
    Last edited by Excel Fox; 08-30-2013 at 11:42 PM. Reason: No sentences in caps allowed! Implied rudeness

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    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),"")
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    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 "," "))
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. How can I make MLOOKUP to ignore blank cells?
    By radionut in forum Excel Help
    Replies: 1
    Last Post: 07-22-2013, 09:18 AM
  2. Find All Empty Blank Cells Or KeyWord In A Column
    By william516 in forum Excel Help
    Replies: 9
    Last Post: 06-25-2013, 05:20 PM
  3. Replies: 13
    Last Post: 06-10-2013, 09:05 AM
  4. Replies: 2
    Last Post: 09-24-2012, 11:19 PM
  5. Highlighting Blank Cells
    By Howardc in forum Excel Help
    Replies: 2
    Last Post: 08-13-2012, 07:56 AM

Tags for this Thread

Posting Permissions

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