+ Reply to Thread
Results 1 to 3 of 3

Concatenating text in multiple columns using IF statements and ignoring blank cells

  1. #1
    Registered User
    Join Date
    03-08-2018
    Location
    Derbyshire, England
    MS-Off Ver
    Excel 2016, Office 365
    Posts
    33

    Concatenating text in multiple columns using IF statements and ignoring blank cells

    Hi,

    I'm concatenating cells in 4 columns using the following formula:

    Please Login or Register  to view this content.
    Initially when I tried concatenating the cells, where there was a blank cell in the middle of the cells on some rows, I switched to this formula to stop multiple commas appearing in the middle of the text, and the formula itself works fine.

    My problem is that due to some poorly formatted data I inherited, on some rows it's the first cell that's blank, and using this formula when the first cell is blank a comma appears before the text. I've tried adding another IF statement to the front of it to check whether D4 is empty, but then realised that it's already checking to see if D4 is empty.

    I did consider wrapping the IF statements in the TRIM function but then realised that wouldn't work because it's extraneous commas I need to trim, not spaces. I can't use TEXTJOIN because although I use Excel 2016 at home, the work PCs are still using Excel 2010. I also considered trying to concatenate a range, but because I'm pulling the data from another worksheet and I'm hiding column G on the original worksheet and not using it in the worksheet where I'm displaying the concatenated text, I can't just specify columns D to H (but the column on the original worksheet can't be deleted because it will be needed once the whole worksheet is updated and it's uploaded back into the system the data's come from.

    After looking around online for a solution I’ve tried numerous variations of nested IF statements, many of which I couldn’t get to work. The nearest one I’ve found to doing the job is this:

    Please Login or Register  to view this content.
    This handles blank cells in any of the columns including where the last column is empty, except for if the first column is blank. I’ve attached a file with some example data on the left, with the results of the formula on the right. The result on row 6 (highlighted in red) is the one that’s displaying incorrectly, with a comma preceding the text from cell F6.

    The formula needs to:
    1. Check if E4 is blank. If it is blank then display nothing. If it isn’t blank then display the contents of the cell followed by a comma
    2. Check if F4 and G4 are blank or have text in them. If they have text then display the text followed by a comma. If they are blank then display nothing
    3. Check if I4 is blank or has text in it. If it has text then display the text. If it’s blank then display nothing.
    4. If I4 is blank then display G4 without a comma after it

    The formula is correctly processing all of these points except the first. I’ve tried the formula below to check whether E4 is blank or contains text:

    Please Login or Register  to view this content.
    The result of this is shown in the example in cell K13 (highlighted in orange) and simply doesn’t show the contents of E13, but displays a comma. I’m sure I’ve the staring this far too long, but I can’t see what the problem is with the formula. Can anyone see where I’m going wrong at all?
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Concatenating text in multiple columns using IF statements and ignoring blank cells

    Try:

    =MID(IF(E4<>"", ","&E4,"") & IF(F4<>"",", "&F4,"") & IF(G4<>"",", "&G4,"") & IF(I4<>"",", "&I4,""),3,1000)

  3. #3
    Registered User
    Join Date
    03-08-2018
    Location
    Derbyshire, England
    MS-Off Ver
    Excel 2016, Office 365
    Posts
    33

    Re: Concatenating text in multiple columns using IF statements and ignoring blank cells

    Quote Originally Posted by Phuocam View Post
    Try:

    =MID(IF(E4<>"", ","&E4,"") & IF(F4<>"",", "&F4,"") & IF(G4<>"",", "&G4,"") & IF(I4<>"",", "&I4,""),3,1000)
    Thank you so much - that works almost perfectly. I haven't come across the MID function before, so I'd have never figured that one out for myself!

    I had to make a couple of slight tweaks to it because I found that it was taking off the first letter of the contents of the first cell with text in, so I changed the start_num argument to 2 to get that first letter back, which worked fine except that on rows where there was no text in the first cell, I ended up with a space infront of the first word. Simply wrapping the whole thing in the TRIM function sorted that though, and it's working perfectly now:

    =TRIM(MID(IF(E4<>"", ","&E4,"") & IF(F4<>"",", "&F4,"") & IF(G4<>"",", "&G4,"") & IF(I4<>"",", "&I4,""),2,1000))

    Thank you again for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 07-26-2017, 12:14 AM
  2. Replies: 3
    Last Post: 07-24-2017, 01:41 PM
  3. [SOLVED] Multiple If Statements if Cells are Blank
    By SlimPickens in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-04-2017, 12:22 PM
  4. [SOLVED] How to ignore blank cells while concatenating multiple cell values
    By RASARO72 in forum Excel General
    Replies: 15
    Last Post: 12-30-2016, 03:13 AM
  5. Concatenating columns ignoring blanks
    By rapscalli in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-11-2015, 11:24 AM
  6. [SOLVED] Sort columns ignoring blank cells
    By maldonadocj in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-16-2014, 07:02 AM
  7. [SOLVED] Sorting a text column while ignoring blank cells
    By mic2mic in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-30-2013, 04:58 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1