Help with concatenate + if !

General discussion on OpenOffice Calc and its spreadsheets.

Help with concatenate + if !

Postby Joseph S. Klein » Sat Oct 18, 2014 10:34 am


Can someone help me to achieve the desired results with a single formula?

A1 -> Ludwig van Beethoven
B1 -> 1770/12/17 - 1827/03/26
result: Ludwig van Beethoven (1770-1827)

A2 -> Jens Becker
B2 -> 1965/05/24
result: Jens Becker (1965)

A3 -> Jim Gilmour
B3 ->
result: Jim Gilmour

What I want is to get the whole A1 cell plus
if the B1 cell has a value, " ("
LEFT (B1, 4)
if there is not a death date, ")"
"-" plus "death year" plus ")"

Thank you,
Joseph S. Klein
Posts: 38
Joined: Mon Mar 24, 2014 6:28 am

Re: Help with concatenate + if !

Postby Bryan L. Warfield » Mon Oct 20, 2014 11:40 am


To fix this problem please follow the instruction given bellow:

Use two helper columns (C, D here) and one column for the result. Put in the formulae
=LEFT(B1;4) | =IFERROR(MID(B1;FIND("- ";B1)+2;4);"") | =A1&IF(C1="";"";" ("&C1&IF(D1="";"";"-"&D1)&")")
respectively and 'Fill Down'.
This if you have the function IFERROR() available. Otherwise the second formula should read:
=IF(ISNUMBER(FIND("- ";B1));MID(B1;FIND("- ";B1)+2;4);"")
The syntax you described by examples will apply strictly.

Bryan L. Warfield
Posts: 29
Joined: Mon Mar 24, 2014 6:11 am

Return to Calc

Who is online

Users browsing this forum: No registered users and 1 guest