Combining Columns In Calc

[stuarts.burgers]

Hi
I have a set of data and I need to combine some cells but not others, with the result of the combined cells I also need to insert the word “and ’ between the to lots of data.
I have attempted to use =CONCATENATE(O5,” and", P5) but I get a redundant “and” in the results cell where I have not combined data from two cells see below
husband and wife.ods

What I want to do is below

husband and wife correct format.ods

I cut and pasted to get the above but I am dealing with sheets of up to 1000 rows and do not have the time to cut and paste

Thanks for your help

Stuart

[anon73440385]

Hello,

try =IF(B2<>"";CONCATENATE(A2;" and ";B2);A2) - but be absolutely sure that column B doesn’t contain anything (even not a space character), if there is no second name. Otherwise formula will fail.

See Husband-and-wife-Modified.ods

Update 1 If you can’t assure, that there are no white spaces in column B, you may use (example for cell C2):

=IF(ISERROR(REGEX(B2;"^\s+$"));IF(B2="";A2;CONCATENATE(A2;" and ";B2));A2)

or you use the same regular expression (^\s+$) in Edit -> Find & Replace to get rid of white spaces.