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 <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
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