Ask Your Question
0

help with formula to insert content of 2 cells into another..

asked 2020-01-20 02:42:57 +0100

clos911 gravatar image

updated 2020-01-20 02:43:41 +0100

I'm using a simple formula to combine the content of two cells into one cell, here's an example..

in my calc file I have A1= 1001 B1= 1020

so my formula in C1 is...

=$A1&"-"$B1

result is 1001-1020

perfect. I pasted the formula into the rest of the cells in my C column.

but now I want to avoid displaying the duplicated numbers in the C cell if.. the cells in the A and B column have the same number. example...

A5= 2001 B5= 2001

so my formula in C5 is...

=$A5&"-"$B5

result is 2001-2001... I would like it to display only 2001

is there a way to adjust this formula so if the 2 cell in A and B column contain the same info it does not display it twice?

any help is appreciated. thank you.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
3

answered 2020-01-20 03:55:38 +0100

robleyd gravatar image

This works for me:

=IF(A5=B5, A5, A5&"-"&B5)

If the values are the same, display the value in A (you could change that to B and have the same effect), otherwise concatenate the two values separated by a dash. Note that the concatenated result is text and will by default display left aligned, unlike numbers which by default display right aligned.

If this answer helped you, please accept it by clicking the check mark ✔ to the left and, karma permitting, upvote it. That will help other people with the same question.

In case you need clarification, edit your question (not an answer) or comment the relevant answer.

edit flag offensive delete link more

Comments

Please use ; semicolon as function parameter separator in formula examples. That way it can be copy-pasted in all locales' environments, not only those that don't use a , comma as decimal separator. Thanks.

erAck gravatar imageerAck ( 2020-01-20 18:44:09 +0100 )edit

@erAck This does not work well, it won't stay with a semicolon, I just tried it and CALC remove the semicolon ; and put back the comma , as separator when I hit enter in the en_(US-UK-PH-....) version and certainly all other locals which do NOT use a comma as decimal separator. (Ubuntu-MATE 19.10.1 - LO 6.3.4.2)

ProLogic gravatar imageProLogic ( 2020-01-21 03:30:43 +0100 )edit

LO Calc will adapt the parameter separator to what it needs, if the semicolon isn't appropriate.

robleyd gravatar imagerobleyd ( 2020-01-21 04:53:24 +0100 )edit

Yes, you'll see the configured function parameter separator in the formula once entered, but all localizations accept the semicolon as separator, hence we use that for examples here so people can easily copy-paste them. (and probably many who answer questions often here have it configured as separator so it's just a matter of copy&paste without replacing anything in the formula which again could be error-prone).

erAck gravatar imageerAck ( 2020-01-22 16:25:33 +0100 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2020-01-20 02:42:57 +0100

Seen: 123 times

Last updated: Jan 20