Re: [Solved] Re: [libreoffice-users] "Case" function equivalent in Calc
That wouldn't change anything. The existing semi-colon would be treated
as just text in the field contents, so it would show up where it is in
the field as well as the formula one. If you already have semi-colons
in the fields at the end of the text, then they wouldn't be needed in my
case. But I didn't have them and didn't want to add them.
On 5/16/13 3:28 PM, mcmurchy1917-libreoff...@yahoo.co.uk wrote:
Just a quick question. What happens if any of the fields already contains a
semi-colon?
Iain
On Thursday 16 May 2013 10:23: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(I
SBLANK(D13))),";","")&B13&IF(NOT(ISBLANK(B13))AND(NOT(ISBLANK(C13)))OR(NOT(I
SBLANK(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
*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"
- aaa. 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:
>>>
>>> PhoneEmailMail -> Phone;Email;Mail
>>> Phone Mail -> Phone;Mail
>>>
>>>Mail -> Mail
>>>
>>> Email
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
*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"
- aaa. 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 0
Re: [Solved] Re: [libreoffice-users] "Case" function equivalent in Calc
Just a quick question. What happens if any of the fields already contains a semi-colon? Iain On Thursday 16 May 2013 10:23: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(I > SBLANK(D13))),";","")&B13&IF(NOT(ISBLANK(B13))AND(NOT(ISBLANK(C13)))OR(NOT(I > SBLANK(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 > > *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" > > - aaa. 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 tw
Re: [Solved] Re: [libreoffice-users] "Case" function equivalent in Calc
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 *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" - aaa. 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: >>> >>> PhoneEmailMail -> Phone;Email;Mail >>> Phone Mail -> Phone;Mail >>>Mail -> Mail >>> EmailMail -> 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+unsubscr...@global.libreoffice.org <mailto:unsubs
[Solved]?? Re: [libreoffice-users] "Case" function equivalent in Calc
Hi :) WoooHooo!! Nicely done! :) Is this whole thread solved now? COngrats of so! Regards from Tom :) > > From: Carl Paulsen >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" >- aaa. 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+unsubscr...@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+unsubscr...@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
Re: [libreoffice-users] "Case" function equivalent in Calc
At long last I got this to work. Syntax is "ISBLANK" and not "ISEMPTY" - aaa. 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 EmailMail -> 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+unsubscr...@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
Re: [libreoffice-users] "Case" function equivalent in Calc
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 EmailMail -> 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 -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] "Case" function equivalent in Calc
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 EmailMail -> 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 -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] "Case" function equivalent in Calc
All I've ever been aware of is nested IF statements, which get to be a royal pain. If there is a CASE equivalent (it's also present in just about any programming language), I'll be just as happy as you! Dave On 5/15/2013 13:16, 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 EmailMail -> 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 -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] "Case" function equivalent in Calc
Another way to state this is I'd like a "Columns to Text" function, but where I can specify semi-colon as the separator and it won't place two separators together consecutively. Thanks again, Carl On 5/15/13 4: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 EmailMail -> 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 -- Carl Paulsen 8 Hamilton Street Dover, NH 03820 (603) 749-2310 -- To unsubscribe e-mail to: users+unsubscr...@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
[libreoffice-users] "Case" function equivalent in Calc
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 EmailMail -> 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 -- Carl Paulsen -- To unsubscribe e-mail to: users+unsubscr...@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
