Solved

Tweaking a concatenated formula in Excel

Posted on 2014-10-14
4
64 Views
Last Modified: 2014-10-14
I am using a simple formula in Column I to concatenate the values in Columns B, F, G & H and insert hyphens in between each string being concatenated. Sometimes there no values in Columns G & H, so the concatenated result ends up with a hyphen dangling at the end. Other times there are no values in B, F, G & H, in which case the concatenated results contains hyphens only. I would like to revise the formula to eliminate the dangling hyphen, and if there are no values, then have the cell remain blank.

I've attached a sample spreadsheet illustrating the existing formula + desired results.

Thanks!
Andrea
Sample-Concatenation.xlsm
0
Comment
Question by:Andreamary
  • 2
4 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40380061
This formula (using the ampersand instead of CONCATENATE) should meet your requirements:
=B2&IF(F2<>"","-"&F2,"")&IF(G2&H2<>"","-"&G2&H2,"")

Regards,
-Glenn
0
 

Author Closing Comment

by:Andreamary
ID: 40380123
Perfect...thanks very much, Glenn!
Andrea
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40380131
You're welcome very much, Andrea. :-)
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40380169
Glenn,  

glad to see you back :-)
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now