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 <carlpaul...@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+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
