0

So I have a Concatenate formula where I take two columns and combine them with text.


=CONCATENATE(B3,".",C3,"@company.com")

It takes my contacts first and last name and generates emails.


Example:

B3: John
C3: Smith

Output: John.Smith@company.com


My issue is it will leave "@company.com" in rows with blank cells.

I don't want that. Please help

Jordan Fitleberg
  • 1
  • 1
  • 1

2 Answers2

0

If you have Excel 2016 or newer, I'd suggest using the TEXTJOIN Function instead of CONCAT, since TEXTJOIN has an option to Ignore Blanks.

With previous versions of Excel, you could build your own version as a custom function, such as this one:

Function TxtJoin(delim As String, ignoreEmpty As Boolean, rg As Range) As String
    Dim c As Range
    For Each c In rg
        If Not ignoreEmpty Or Not IsEmpty(c) Then TxtJoin = TxtJoin & delim & c
    Next c
    If TxtJoin <> "" Then TxtJoin = Mid(TxtJoin, Len(delim) + 1)
End Function

This is from another answer of mine here.

There's also a similar Q&A here.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
0

suggest you use simple If with the use of cencatenate. i used AND also because i thought both b3 and c3 needs to be empty, try it out. if only another one is empty it will do the formula

=IF(AND(B3="",C3=""),"",CONCATENATE(B3,".",C3,"@company.com"))

like so enter image description here

Mikael
  • 57
  • 5