Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jul 2003
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenate columns

    Hello -
    This seems like it should be simple but I have not been successful so far. In the attachment I am trying to concatenate three columns but if all three are blank then I want n/a to appear.

    Can anyone help?
    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    Jul 2013
    Location
    Hamilton, ON
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You can check for cells having spaces as well as empty with this formula:

    =IF((CONCATENATE(TRIM(A3),TRIM(B3),TRIM(C3))="")," N/A",CONCATENATE(A3,B3,C3))

    Regards, Rick

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Konopca,

    Rick beat me to the post but here's a slightly different version.

    This should do the trick:
    =IF(AND(ISBLANK($A1),ISBLANK($B1),ISBLANK($C1)),"N/A",CONCATENATE(A1,B1,C1))
    enter into D1 and fill down. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    ..and another way would be to use a custom formula, see =koncatenate(A1:C1) in attached file [rz1a-TEST1.xlsm].
    This uses a version of my concatenate function, described with examples, in attached file [zeddy-myCONCAT-function-version1a.xls]

    zeddy
    Attached Files Attached Files

  5. #5
    Lounger
    Join Date
    Jul 2003
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks to all of you. Retired Geek, I used your suggestion, it worked like a charm.
    Rfallen, your solution gave me TRUE and FALSE values and Zeddy yours was a little to sophisticated for me to understand being the novice Excel user that I am.
    Thanks again.
    Carol.

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Konopca,

    Keep in mind that there is a difference between Rick's (rfallen) solution and RG's solution. Rick's solution will check to see if ALL 3 cells only have spaces (that's what TRIM does). Sometimes, people inadvertently hit a space key. Rick's formula checks for that. RG's formula does not: if one cell has spaces and the others are really blank, RG's formula will not give you the N/A.

    However, it does not appear that Rick's formula accounts for the following: only concatenate non-blank cells (where a blank cell would also include one where there are just spaces). If one cell is " " (2 spaces), then you'll get 2 spaces somewhere in your concatenation. If you account for the spaces in all 3 cells, you might want to account for the spaces in ANY of the 3 cells and only concatenate cells with non-blanks/spaces.

    Rick's formula did work for me - no TRUE/FALSE.

    Fred

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Carol

    You are right - RG's solution does exactly what you asked for:
    =IF(AND(ISBLANK($A1),ISBLANK($B1),ISBLANK($C1)),"N/A",CONCATENATE(A1,B1,C1))

    Now, if you had to do this for say, columns A to P, then RG's solution would get very long and tedious.
    Whereas my
    =koncatenate(A1:C1)
    simply becomes..
    =koncatenate(A1:P1)

    The other sample file I attached was really for the benefit of other Lounge users who may want to see a useful CONCATENATE function which Excel lacks.

    zeddy

  8. #8
    Lounger
    Join Date
    Jul 2003
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Fred and zeddy for the additional information. Even though I only posted a sample of my spreadsheet, the entire spreadsheet is still only just a few columns and would not contain any spaces in the columns in question.

    Now I know where to come for Excel questions!

Posting Permissions

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