• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

Tweaking a concatenated formula in Excel

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
Andreamary
Asked:
Andreamary
  • 2
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
This formula (using the ampersand instead of CONCATENATE) should meet your requirements:
=B2&IF(F2<>"","-"&F2,"")&IF(G2&H2<>"","-"&G2&H2,"")

Regards,
-Glenn
0
 
AndreamaryAuthor Commented:
Perfect...thanks very much, Glenn!
Andrea
0
 
Glenn RayExcel VBA DeveloperCommented:
You're welcome very much, Andrea. :-)
0
 
ProfessorJimJamCommented:
Glenn,  

glad to see you back :-)
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now