| View previous topic :: View next topic |
| Author |
Message |
PORTOUX
Newbie
Joined: 31 Oct 2008
Posts: 4
Location: FRANCE
|
 Posted: Fri Oct 31, 2008 3:36 am Post subject: Use of asterisk (*) in Calc functions |
 |
|
I can't figure out how to make the asterisk work in this simple Calc function the way it does in Excel.
=SUM.IF(A1:A9;B2&C2&"*";D1:D9)
Can someone help me out with this, it's driving me crazy !
Thanks
Portoux |
|
| Back to top |
|
 |
bobban
OOo Enthusiast
Joined: 02 Jan 2008
Posts: 172
Location: Australia
|
| Posted: Fri Oct 31, 2008 4:00 am Post subject: |
|
|
hi PORTOUX,
I think you just want this:
=SUM.IF(A1:A9;B2&C2&".*";D1:D9)
Excel uses wildcards I believe, which means * is just a replacement for anything.
OO uses regular expressions.
_________________
nil sine labore |
|
| Back to top |
|
|
PORTOUX
Newbie
Joined: 31 Oct 2008
Posts: 4
Location: FRANCE
|
| Posted: Fri Oct 31, 2008 4:24 am Post subject: |
|
|
I already tried this before and it doesn't work.
Thank you though, any other ideas ?
That's right * is just a replacement for anything in Excel. |
|
| Back to top |
|
|
bobban
OOo Enthusiast
Joined: 02 Jan 2008
Posts: 172
Location: Australia
|
| Posted: Fri Oct 31, 2008 4:43 am Post subject: |
|
|
Please explain your purpose more.
I thought you were looking for words in column A, that are the concatenation of the strings in B2 and C2, with any number of extra characters following.
So if:
B1 = "h"
C1= "o"
then searching for B2&C2&".*", means "ho*" where * can be any number of extra characters. ie.
A1 = "hope" - yes
A1 = "holly" - yes
A1 = "ho" - yes
A1 = "half" - no
Here is the file I just experimented with. Have a look and see if this is what you want.
http://www.4shared.com/file/69131021/737c5225/Sumif_and__regex.html
_________________
nil sine labore |
|
| Back to top |
|
|
PORTOUX
Newbie
Joined: 31 Oct 2008
Posts: 4
Location: FRANCE
|
| Posted: Fri Oct 31, 2008 6:03 am Post subject: |
|
|
That is exactly what I am trying to do and you are right, it works if I do it from scratch in a new .ods file.
BUT it does not work when I convert an excel file to .ods file and modify the formula in Calc. Any idea why that is ?
It is just that I already have this big excel spreadsheet that I would like to convert to Calc without having to start everything from scratch.
Thank you very much for all the help. |
|
| Back to top |
|
|
bobban
OOo Enthusiast
Joined: 02 Jan 2008
Posts: 172
Location: Australia
|
| Posted: Fri Oct 31, 2008 6:14 am Post subject: |
|
|
Ahhh, interesting. There is some funny business that happens when xls is converted to ods. This thread might be your answer courtesy of Villeroy:
http://www.oooforum.org/forum/viewtopic.phtml?t=55549&highlight=xls+ods+regular
Excel does not user regular expressions, so when OO opens an XLS file it assumes that it does not have regular expressions. Perhaps even after converting to ODS the option for regular expressions is not enabled. Check it here:
Tools->Options->OpenOffice.org Calc->Calculate->"Enable regular expressions in formulas"
_________________
nil sine labore |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Fri Oct 31, 2008 8:01 am Post subject: |
|
|
| bobban wrote: | hi PORTOUX,
I think you just want this:
=SUMIF(A1:A9;B2&C2&".*";D1:D9) [Edited: no period in SUMIF]
Excel uses wildcards I believe, which means * is just a replacement for anything.
OO uses regular expressions. |
This is on track. But first go to Tools/Options/Calc, and in Calculate, toggle on Enable Regular Expressions.
David. |
|
| Back to top |
|
|
PORTOUX
Newbie
Joined: 31 Oct 2008
Posts: 4
Location: FRANCE
|
| Posted: Fri Oct 31, 2008 9:53 am Post subject: |
|
|
The option for regular expressions was not enabled. It enabled it and now it works like a charm. Thanks to all !
 |
|
| Back to top |
|
|
|
