Re: [libreoffice-users] sort order

[Brian Barker]

At 17:58 11/03/2017 +, Gary Collins wrote:
I'd like to know if there is a character, preferably some sort of 
discrete punctuation mark, that will come *after* z in the sort 
order when using natural sort. My problem is that i have some codes 
beginning Aa which really speaking should come after Z. As a number 
of up to 3 digits follows the letter(s) in each case, im getting A# 
followed by Aa# then B# which really wont do. I have to use natural 
sort as i need eg A12 to come after A3. Ive tried a few punctuation 
characters and some special characters but i cant find one that 
works. Any light that can be shed on this would be very welcome 
indeed. I really dont want to have to precede Aa with a z if that 
can possibly be avoided.


These are not punctuation marks, but a bit of experimentation 
suggests that any Greek character, upper or lower case, will serve.


I trust this helps.

Brian Barker


--
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] regular expression for calc

[Brian Barker]

At 15:45 11/03/2017 +, Gary Collins wrote:
I have a column each row of which contains a sequence of one or more 
codes separated by a space. In case it will make a difference each 
code consists of a letter (occasionally 2 letters) followed by a 
number of up to 3 digits (and occasionally ends with a letter) eg X1 
Aa12 D7a etc.


I want to obtain a regular expression for COUNTIF that will enable 
me to find out how many times a particular code, contained in 
another cell, occurs in the column. I was thinking that \b to detect 
the word boundaries should help but i cant quite get it to work; im 
not sure if this is because it is assuming a word boundary between 
the letter and the number or if it is simply my incompetence as 
usual. Does anyone have an idea what may be the best way to achieve this?


Try
=COUNTIF(range;"\b"&"\b")
- where "range" represents the column of data and Xn represents the 
cell containing the code to be counted. Remember that you need to 
have Tools | Options... | LibreOffice Calc | Calculate | Enable 
regular expressions in formulae ticked (which is the case by default).


I trust this helps.

Brian Barker


--
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] sort order

[leleu]

Just google for "characters sorting order"...you'll have to choose by 
yourself...



Je la 11/03/2017 18:58, Gary Collins skribis :

Hello
Id like to know if there is a character, preferably some sort of discreet 
punctuation mark, that will come *after* z in the sort order when using natural 
sort. My problem is that i have some codes beginning Aa which really speaking 
should come after Z. As a number of up to 3 digits follows the letter(s) in 
each case, im getting A# followed by Aa# then B# which really wont do. I have 
to use natural sort as i need eg A12 to come after A3. Ive tried a few 
punctuation characters and some special characters but i cant find one that 
works. Any light that can be shed on this would be very   welcome indeed. I 
really dont want to have to precede Aa with a z if that can possibly be avoided.
Thanks
Gary



--
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] vlookup case

[Gary Collins]

Wow! Thanks i'll look into that and maybe try to adapt it.  The strings are of 
variable length from 1 to 3 characters. What i came up with (bypassing VLOOKUP 
altogether) was
=CONCATENATE (MID ($J$2, FIND(LEFT (A2,1), $J $3),1),IFERROR(MID($J$2, FIND 
(MID (A2,2,1), $J $3),1," "),IFERROR (MID ($J $2,FIND (MID (A2,3,1), $J 
$3,1),1," "))

(I think); which takes each character from the string in A2, gets the position 
of that character in the string held in J2, then extracts the corresponding 
character from the string held in J3 and concatenates it to the result. In the 
case of the string being less than 3 characters, MID () will return an error 
which will result in " " concatenated instead. Null string would have been 
better but in my case space works just as well.

Its very cumbersome (and more so because in my actual file the strings J2 and 
J3 are in fact located on a different sheet); but it does what i need and thats 
all i demand of it!

Best
Gary

On Fri, 10/3/17, Michael D. Setzer II  wrote:

 Subject: Re: [libreoffice-users] vlookup case
 To: "Gary Collins" , users@global.libreoffice.org, 
"Brian Barker" 
 Date: Friday, 10 March, 2017, 11:25
 
 I was doing some testing
 with single characters, but then saw it was three
 characters. Originally, just used the code of
 the character, changed code to
 ascii, but
 with 3 multiple each value to get a number that matched.
 In both put the row number in next column.
 
 Put the match field in C15
 both the formulas work, both formulas give the
 row.
 
 
 abc
                  
                                        
          1
            
                                        
         97098099
                
                                        
            1
 
 
 
 aBc
                                
                                
    2
                  
                                        
   97066099
                      
                                        
      2
 
 
 
 aBC
      
                                        
                      3
                                
                             97066067
                                
                                
    3
 
 
 
 Abc
      
                                        
                      4
                                
                             65098099
                                
                                
    4
 
 
 
 AbC
      
                                        
                      5
                                
                             65098067
                                
                                
    5
 
 
 
 Abc
      
                                        
                      6
                                
                             65098099
                                
                                
    6
 
 
 
 Ab
        
                                        
                    7
  
                                        
                   65098000
      
                                        
                      7
 
 
 
 aCb
                  
                                        
          8
            
                                        
         97067098
                
                                        
            8
 
 
 
 AcB
                                
                                
    9
                  
                                        
   65099066
                      
                                        
      9
 
 
 
 aBC
      
                                        
                     10
        
                                        
             97066067
            
                                        
               10
 
 
 
 AAC
      
                                        
                     11
        
                                        
             65065067
            
                                        
               11
 
 
 
 ss
        
                                        
                   12
          
                                        
          115115000
        
                                        
                   12
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 aBC
 
                                
                             97066067
 
 
 
 
 
 
 
 
 
 
 
        
                                        
                    3
 “=VLOOKUP($C$15,C1:D12,2,0)
 
                    
                                        
        3
 “{=INDEX(B1:B12,MATCH(1,EXACT($C$15,A1:A12),0))}
 
 
 
 
 
 
 
 
 Formula in
 C
 
 
 
 
 
 
 “=CODE(MID(A15,1,1))*100+CODE(MID(A15,2,1))*1000+CODE(MID(A15,3,1))
 
 
 On 10 Mar
 2017 at 9:46, Gary Collins wrote:
 
 Date sent:          Fri, 10 Mar 2017
 09:46:22 + (UTC)
 From:       
    Gary Collins 

[libreoffice-users] sort order

[Gary Collins]

Hello
Id like to know if there is a character, preferably some sort of discreet 
punctuation mark, that will come *after* z in the sort order when using natural 
sort. My problem is that i have some codes beginning Aa which really speaking 
should come after Z. As a number of up to 3 digits follows the letter(s) in 
each case, im getting A# followed by Aa# then B# which really wont do. I have 
to use natural sort as i need eg A12 to come after A3. Ive tried a few 
punctuation characters and some special characters but i cant find one that 
works. Any light that can be shed on this would be very   welcome indeed. I 
really dont want to have to precede Aa with a z if that can possibly be avoided.
Thanks
Gary

-- 
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] regular expression for calc

[Gary Collins]

Apologies if this appears twice; im not sure if there was a problem with 
sending...

Hello,
I have a column each row of which contains a sequence of one or more codes 
separated by a space. In case it will make a difference each code consists of a 
letter (ooccasionally 2 letters) followed by a number of up to 3 digits (and 
occasionally ends with a letter) eg X1 Aa12 D7a etc.

I  want to obtain a regular expression for COUNTIF that will enable me to find 
out how many times a particular code, contained in another cell, occurs in the 
column. I was thinking that \b to detect the word boundaries should help but i 
cant quite get it to work; im not sure if this is because it is assuming a word 
boundary between the letter and the number or if it is simply my incompetence 
as usual.

Does anyone have an idea what may be the best way to achieve this?

Thanks
Gary

-- 
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] regular expression for calc

[Gary Collins]

Hello,
I have a column each row of which contains a sequence of one or more codes 
separated by a space. In case it will make a difference each code consists of a 
letter (ooccasionally 2 letters) followed by a number of up to 3 digits (and 
occasionally ends with a letter) eg X1 Aa12 D7a etc.

I  want to obtain a regular expression for COUNTIF that will enable me to find 
out how many times a particular code, contained in another cell, occurs in the 
column. I was thinking that \b to detect the word boundaries should help but i 
cant quite get it to work; im not sure if this is because it is assuming a word 
boundary between the letter and the number or if it is simply my incompetence 
as usual.

Does anyone have an idea what may be the best way to achieve this?

Thanks
Gary

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