2013 Archives by date, by thread · List index

# Re: [Solved] Re: [libreoffice-users] "Case" function equivalent in Calc

```Here's a formula that works.  It's ugly, but it works.

=MID(SUBSTITUTE(SUBSTITUTE(";"&A1&";"&B1&";"&C1&";"&D1&";",";;",";"),";;",";"),2,LEN(SUBSTITUTE(SUBSTITUTE(";"&A1&";"&B1&";"&C1&";"&D1&";",";;",";"),";;",";"))-2)

```
The thing to do in a case like this is to break the problem down into sections. There are many ways to approach this specific question, but here's my thought process.
```
```
1. Go ahead and concatenate all the cells and worry later about whether or not they contain data. Insert semicolons between values.
```
```
2. Include a semicolon at the beginning and end of the string to accommodate the next two steps (in case the first or last cell is empty).
```
3.  Where two semicolons are consecutive, eliminate one of them.

4.  Eliminate the semicolons at the beginning and end of the string.

```
After that, I break the functions down to simplify construction of the monster.
```
A.  =MID([string],2,LEN([string]-2)
This strips the beginning and ending semicolons.

B.  =SUBSTITUTE([string],";;",";")
```
This replaces every instance of two semicolons with a single one. Unfortunately, the function is not recursive, so if there are three in a row it will only make one replacement. This why the final formula has two instances of the SUBSTITUTE function.
```
C.  [string] is the concatenation of everything.

Hope this helps!

Dave

On 5/16/2013 07:23, Carl Paulsen wrote:
```
```Hi Tom.

```
Well, not quite. In my particular case this formula worked. But with some off-list input from smarter brains than mine, I realize it wouldn't work under many situations.
```
```
It turns out the formula would need to change a bit, otherwise what I did would leave a trailing semi-colon where column X is blank. In fact, it's a tricky situation b/c a semi-colon is needed after U3 if U3 has a value AND any of the others has a value, otherwise not. Then, if V3 has a value and any of the other later cells has a value, there should be another semi-colon. Etc. etc. In the last case, a semi-colon should follow W3 only if there's a value in X3.
```
```
I've tried a bunch of formulas and the closest I've come is the following:
```
```
=A13&IF(NOT(ISBLANK(A13))AND(NOT(ISBLANK(B13)))OR(NOT(ISBLANK(C13)))OR(NOT(ISBLANK(D13))),";","")&B13&IF(NOT(ISBLANK(B13))AND(NOT(ISBLANK(C13)))OR(NOT(ISBLANK(D13))),";","")&C13&IF(NOT(ISBLANK(C13))AND(NOT(ISBLANK(D13))),";","")&D13
```
```
But that clearly doesn't work. Note I'm working in a test sheet and am using different cells. This formula leaves leading semi-colons under a number of situations and double semi-colons under others. I'm not entirely sure CASE would work any better here though.
```
```
Anyone want to help out, great. But it's a pretty specialized situation, so I understand if not.
```
Carl

On 5/16/13 3:02 AM, Tom Davies wrote:
```
```Hi :)
```
WoooHooo!! Nicely done! :) Is this whole thread solved now? COngrats of so!
```Regards from
Tom :)

------------------------------------------------------------------------
*From:* Carl Paulsen <carlpaulsen@comcast.net>
*To:* users@global.libreoffice.org
*Sent:* Thursday, 16 May 2013, 1:44
```
*Subject:* Re: [libreoffice-users] "Case" function equivalent in Calc
```
At long last I got this to work.  Syntax is "ISBLANK" and not
"ISEMPTY"
```
- aaarrrrgggghhhh. My Filemaker days are getting in my way. Replace
```    all "isempty" below with "ISBLANK" and it works perfectly now.

Carl

On 5/15/13 6:38 PM, Carl Paulsen wrote:
> So in the absence of a Case function, here's what I've done so
far as
> a calculated solution.  Note that the data I want to concatenate
is in
> cells U3, V3, W3, and X3.  In Y3, I put the following:
>
> =U3 & IF(NOT(isempty(U3)),";","") & V3 &
IF(NOT(isempty(V3)),";","") &
> W3 & IF(NOT(isempty(W3)),";","") & X3
>
> The idea is that I put together U3, a semicolon if U3 isn't
empty (and
> nothing if it is), V3 and a semicolon if V3 isn't empty, W3 and a
```
> semicolon if W3 isn't empty, and X3. If any of the cells is empty,
```    > nothing will be added until the next cell that has data.
>
> Unfortunately, I'm getting a #NAME? error.  I'm assuming some
kind of
> syntax error.  Any words of wisdom?
>
> Thanks a ton all.
> Carl
>
>
>
> On 5/15/13 5:13 PM, Dan Lewis wrote:
>> On 05/15/2013 04:16 PM, Carl Paulsen wrote:
```
>>> Anyone know if there is an equivalent to the Filemaker Pro "Case"
```    >>> function?  It's kinda like the "IF" function but is simpler to
>>> concatenate multiple conditions. It basically says If
something is
>>> true then do what is specified, if the next thing is, then do
that,
>>> if the next thing is true, do that, etc.
>>>
>>> Here's what I need to do.  Take 4 columns and concatenate with a
>>> semi-colon between the values, but not string together two
>>> semi-colons consecutively.  Like:
>>>
>>> Phone    Email    Mail -> Phone;Email;Mail
>>> Phone                  Mail -> Phone;Mail
>>>                            Mail -> Mail
>>>              Email    Mail -> Email;Mail
>>>
>>> So semi-colons only occur if there's a value present and not
at all
>>> if there's only one value present.
>>> I hope that makes sense and displays correctly.
>>>
>>> Carl
>>      I just checked the available functions in Calc. The "Case"
>> function does not appear among them. "Case When" is available
in Base
>> database queries though.
>>
>> --Dan
>>
>
>

--
Carl Paulsen

8 Hamilton Street

Dover, NH 03820

(603) 749-2310

```
-- To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
```    <mailto:unsubscribe@global.libreoffice.org>
Problems?
http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more:
http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and
cannot be deleted

```
```
```
```

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
```