Start Free TrialLog in
Avatar of Ted Penner
Ted PennerFlag for United States of America

asked on 

Concatenate non-empty cells

This command should concatenate the cells and add a space before the next cell, but it should do this only if the cell is not empty, and this is where I am having trouble.
=CONCATENATE(AL4&", ",AM4&", ",AN4&", ",AO4)

Open in new window

ProgrammingSoftwareXMLSpreadsheetsMicrosoft Excel
Avatar of undefined
Last Comment
byundt
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie
Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of byundt
byundt
Flag of United States of America image
TEXTJOIN is the way to do it.

If you are stuck with a legacy version of Excel, you need to concatenate the results of IF functions:
=MID(IF(AL4="","",", " & AL4) & IF(AM4="","",", " & AM4) & IF(AN4="","",", " & AN4) & IF(AO4="","",", " & AO4),3,999)

Open in new window

The MID function strips the leading comma and space from the concatenated text.
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts

  • "Invaluable tool for our organization"

    Josh Regalski

  • "Your help has saved me hundreds of hours of internet surfing."

    @fblack61

  • "Just a dang good service!"

    @golferski

  • "Worth every penny."

    Steve Hougom

  • "It’s been a life saver."

    Maria Halt

  • "Best support site I’ve seen!"

    Bob Schneider

  • "I would be lost without them."

    @fblack61

  • "Saved my job multiple times."

    Walt Forbes

  • "I love this site."

    Maria Duwe

  • "Friendly respectful help."

    Andrew Kowtalo

  • "Such top-notch experts."

    @magento

  • "The best money I have ever spent."

    @rwheeler23

  • "Beyond any comprehensible value"

    George Morris

  • "Invaluable tool for our organization"

    Josh Regalski

Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo
© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent