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

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

clos911
84 ●22 ●32 ●40

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

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.

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

robleyd

4385 ●3 ●12 ●33

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

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 ( 2020-01-20 18:44:09 +0200 )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 ( 2020-01-21 03:30:43 +0200 )edit

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

robleyd ( 2020-01-21 04:53:24 +0200 )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 ( 2020-01-22 16:25:33 +0200 )edit

add a comment

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

1 Answer

Comments

Question Tools

Stats

Related questions

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

1 Answer

Comments

Question Tools

Stats

Related questions

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