Ask Your Question

Combining Columns In Calc

asked 2019-09-11 10:38:06 +0200

stuarts.burgers gravatar image

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 C:\fakepath\husband and wife.ods

What I want to do is below

C:\fakepath\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


edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-09-11 10:58:31 +0200

Opaque gravatar image

updated 2019-09-11 13:42:35 +0200


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 C:\fakepath\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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-09-11 10:38:06 +0200

Seen: 19 times

Last updated: Sep 11