Excel concatenation syntax where one specific field (of three fields) may be Nul

Question

Excel concatenation syntax where one specific field (of three fields) may be Null

Hello all

I'm having a problem with EXCEL 2007 concatenation syntax. I have three fields [LastName], [Husband'sFirstName] and [Wife'sFirstName].

Where the [Wife'sFirstName] is null the solution should be - [HusbandsFirstName] [LastName] (e.g., Bob Wald).

Where the [Wife'sFirstName] is NOT null the solution should be - [Husband'sFirstName] and [Wife'sFirstName] [LastName] (e.g., Bob and Mariann Wald).

I have been trying various version of -- FullName: [Husband'sFirstName] & " " & If([Wife'sFirstName] Is Null,[LastName],& " and " &[Wife'sFirstName2] & " " & [LastName]) with no luck.

Thanks in advance for any help

Explanation / Answer

Try:

=TRIM(IF(HusbandFirstName="",WifeFirstName,IF(WifeFirstName="",HusbandFirstName,HusbandFirstName & " and " & WifeFirstName)) & " " & LastName)

With the TRIM function, if both first names are absent, the formula will return just the last name; and if there are no names, it will return a blank (with no spaces). It will also properly return data if there is just one or both first names.

Here is a slightly shorter version:

=TRIM(HusbandFirstName & IF(COUNTA(WifeFirstName,HusbandFirstName)=2," and ","") & WifeFirstName & " " & LastName)

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.