CODEWITHSUNDEEP
excel-formulaconcatenationlibreoffice-calc
Lou
Lou

Reputation: 2509

Specific concatenation of text cells by formula

I'm having difficulty producing a CONCATENATE formula that combines text cells in the way that I want. There are five fields that I want to concatenate: Title, Forename, RegnalNumber, Surname, and Alias, in that order. I'm no regex expert, so excuse the poor formatting, but this is a rough way of expressing what I'm trying to achieve:

(title)? (forename) (regnalnumber)? (surname)?, (alias).

The only field that can't be null is the forename field, although it might have the value "?", in which case it shouldn't output anything in the concatenation, i.e. it should be treated as blank. Hopefully the following test cases should demonstrate the output I'm trying to achieve: the output on the right is what it should look like:

| Title  | Forename | RN | Surname   | Alias        | CONCATENATE                           |
+--------+----------+----+-----------+--------------+---------------------------------------+
| Ser    | Jaime    |    | Lannister | Kingslayer   | Ser Jaime Lannister, Kingslayer       |
|        | Pate     |    |           |              | Pate                                  |
| Lord   | ?        |    | Vance     |              | Lord Vance                            |
| King   | Aerys    | II | Targaryen | The Mad King | King Aerys II Targaryen, The Mad King |
| Lord   | Jon      |    | Arryn     |              | Lord Jon Arryn                        |
|        | Garth    |    |           | Of Oldtown   | Garth, Of Oldtown                     |

I've experimented for ages trying to make this concatenation work, but haven't been able to get it right. This is the current formula, with cell references replaced by the field name for comprehensibility:

=CONCATENATE(IF(Title<>"",Title&" ",""),IF(AND(Forename<>"",Forename<>"?"),Forename,""),IF(RN<>""," "&RN,""), IF(OR(AND(Forename<>"", Forename<>"?"), Surname<>"", RN<>""), " ",""), IF(Surname<>"",Surname,""),IF(AND(Alias<>"",OR(Alias<>"",AND(Forename<>"", Forename<>"?"),Surname<>"")),", "&Alias, Alias))

There is one case where it doesn't work: if the Surname and RN are null but the the Forename and Alias are non-null. For example, if the Forename is Garth, and the Alias is Of Oldtown, the concatenation outputs: Garth , Of Oldtown. It's the same if the title is non-null. It shouldn't have a space before the comma.

Can you help me to fix this formula so it works as expected? If you can find a way to simplify it, even better! I know I'm probably overcomplicating this a great deal. I'm using LibreOffice Calc 4.3.1.2, not Excel.

Upvotes: 1

Views: 107

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34230

I know this is only a bit of fun, but can I suggest a more algorithmic approach?

The algorithm is:-

If a field is empty or ?, do nothing

Else

  If concatenation so far is empty, add field to concatenation

  Else

    Add a space followed by the field to concatenation

which leads to this formula in G2 :-

=IF(OR(A2="",A2="?"),F2,IF(F2="",A2,F2&" "&A2)

(need to put single apostrophes in column F to make it work)

which when copied across looks like this:-

enter image description here

Upvotes: 1

Richard Le Mesurier
Richard Le Mesurier

Reputation: 29722

The best way imho to solve situations like this is to divide the problem over multiple simple columns, rather than 1 huge complex formula. Remember you can always hide the columns that you don't want to see.

So create a column for Title that says =if(a2="","",a2&" ").

That can be extended for all the other columns, except:

  • for Forename, where you want to include the "?" as follows: =if(b2="?","",b2&" ")
  • for Alias, where you want to include the leading ",": =if(e2="","",", "&e2)

Lastly just concatenate each of your working columns with something like: =f2&g2&h2&i2&j2.

This breaks the problem down into very simple components, and makes it easy to debug. If you want to add extra functionality at a later stage, it is easy to swap out one of your formulae for something else.

Upvotes: 1

Related Questions