CODEWITHSUNDEEP
excelstringexcel-formulaconcatenation
lexi
lexi

Reputation: 17

Excel Combine Cell Strings and Ignore Blank Values

I have 3 columns of data that I want to combine into the format AAAA.BB-CC

Columns A and B will always have values, but column C is optional. In that case, I want to ignore the blanks and have the result written as AAAA.BB

The problem I have with my formula as it is now, is that the "-" will always show, even if column C is blank

='Set-Up'!$B$4 &"." &[MMC Code] & "-"&[Sub MMC Code]

The AAA portion comes from a different sheet, and is the same value down the list.

Upvotes: 0

Views: 89

Answers (2)

bosco_yip
bosco_yip

Reputation: 3802

Another option

=SUBSTITUTE(TRIM('Set-Up'!$B$4&"."&[MMC Code]&" "&[Sub MMC Code])," ","-")

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152505

use an IF()

='Set-Up'!$B$4 &"." &[MMC Code] & IF([Sub MMC Code]<>"","-"&[Sub MMC Code],"")

Upvotes: 2

Related Questions