+ Reply to Thread
Results 1 to 4 of 4

deleting blank rows in a concatenate formula

  1. #1
    Registered User
    Join Date
    04-20-2013
    Location
    texas
    MS-Off Ver
    Excel 2010
    Posts
    2

    deleting blank rows in a concatenate formula

    I am trying to bring text data from multiple worksheets into a final worksheet by using concatenate...unfortunately i cannot figure out how to have it remove the rows with no text data, it just places a blank line instead. This makes the cell extremely large when it doesn't need to be.

    Here is an example of my formula: =CONCATENATE('Beaty, PJ'!K7,CHAR(10),'Bubela, G'!K7,CHAR(10),'Carreon, P'!K7,CHAR(10),'Cope, K'!K7,CHAR(10),'Cummings, PC'!K7,CHAR(10),'Hagemeier, D'!K7,CHAR(10),'Hasty, B'!K7,CHAR(10),'Higgins, J'!K7,CHAR(10)) Any help will be greatly appreciated.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: deleting blank rows in a concatenate formula

    Perhaps
    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: deleting blank rows in a concatenate formula

    =TRIM(yourformula)

    Try using that. If it doesn't do what you need then try:

    =CONCATENATE(IF(A="","",A&CHAR(10)),IF(B="","",B&CHAR(10)))

    replacing A and B with the cells you want to join, repeating as needed.
    Say thanks, click *

  4. #4
    Registered User
    Join Date
    04-20-2013
    Location
    texas
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: deleting blank rows in a concatenate formula

    Thank you so much...trim did not work but the second suggestion did.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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