Concatenate Two Columns with a Dash, Skipping Some Blanks

[JeremyS]

Hello all,

I searched around on the forum for an answer for this, and I found some solutions that were close. I was not successful at adapting them to my needs (and I feel like this should be easy!).

I have a spreadsheet with testing data (quality control tolerances), so there is a column with the low limit of the testing specifications, and a column with the high limit of testing specifications. I want to be able to join to the two columns with a dash in between the numbers, so there is a single column showing the full range of quality control tolerances. Where the problem lies is sometimes the low range column is blank, sometimes the high range column is blank, and sometimes both columns are blank. The only time I need a dash is when both columns have some type of data. I've tried nesting IF and ISBLANK functions to no avail (they will leave blanks and concatenate when both cells in both columns are populated, but will leave a trailing or leading dash when one of the cells is filled and the other is blank). Here is a table with hypothetical data showing what the concatenate should look like, just so it is a little easier to see. It closely approximates the data I have. Thanks in advance for the help.

[TABLE="width: 500"]
<tbody>[TR]
[TD]TestSpecificationRange[/TD]
[TD]LowLimit[/TD]
[TD]HighLimit[/TD]
[/TR]
[TR]
[TD]7-9[/TD]
[TD]7[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.001[/TD]
[TD]0.001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8-13[/TD]
[TD]8[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]0.005-0.25[/TD]
[TD]0.005[/TD]
[TD]0.25[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10-13.5[/TD]
[TD]10[/TD]
[TD]13.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[S.H.A.D.O.]

Good afternoon,

Just as a general solution I would think something along the lines of...

=CONCATENATE(A1,"-",B1)

Obviously change the column letter and row number to suit.

I hope this helps.

 

[CodeNinja]

=IF(OR(ISBLANK(B2),ISBLANK(C2)),B2&C2,B2&"-"&C2)

That will not give you -0 or 1-...

Good luck,

CN.

 

[Rick Rothstein]

Here is another formula for you to consider...

=SUBSTITUTE(TRIM(B2&" "&C2)," ","-")

 

[JeremyS]

Here is another formula for you to consider...

=SUBSTITUTE(TRIM(B2&" "&C2)," ","-")

Thank you, Rick. This worked perfectly for what I was looking to do.