 | |
#1
 
| |||
| |||
|
First page -
01-08-2010
, 01:50 AM
#2
| |||
| |||
|
|
Hi, I wrote first my question in french on an english-speaking newgroup. Sorry. Anyway it was poorly phrased. So I go again with my question. A book, as most written materials are always page numbered. Often on a DB, the 'page' reference is filled up with a lot of characters which give multiple pages and/or special characters. One of my client (using FMP6) wishes to extract from that field a unique page number. He chooses to take the first valid number. All the following examples (take off the asteriscs) are supposed to give the 124 number as the first page : *124*; *(124)*; *124-126*; *124 et 125*; *124-126 et 200,201*; *124 et 200(?)*; *124, 125, 127-130*; *(?)124-126*; *124A et 124B*; ... I tried a formula approach using the 'substitute' function, but it often gave strange results. I suppose a better way would be to scan the 'page' field character by character, and disregard the non-numeric ones at least for the characters at the beginning. Then take the first number, first meaning that the search would stop when getting a non-numeric character. Say also as a limit that books pages never go beyond 4 digits. But I don't know how to specify a 'numeric character' in order to isolate it from the junk, nor recognize the 'non-numeric characters', nor setting up the whole system to achieve the task. Could you help me ? Many thanks. Remi-Noel |
#3
| |||
| |||
|
|
Remi-Noel I think the Filter function wil be helpfull. Filter ( MyField ; "1234567890- ," ) *124* returns 124 *(124)* returns 124 *124-126* returns 124-126 (take all characters left of the -) *124 et 125* returns 124 125 ( take all characters left of the space ) ; *124-126 et 200,20177*; returns 124-126 200,20177 (Take all characters left of the -) etc etc You will have to expand the string between the "" inside the calculation to suit your needs -- Keep well / Hou je goed Ursus "Remi-Noel Menegaux" <rnmenegaux (AT) free (DOT) fr> schreef in bericht news:4b46e3dc$0$30635$426a74cc (AT) news (DOT) free.fr... Hi, I wrote first my question in french on an english-speaking newgroup. Sorry. Anyway it was poorly phrased. So I go again with my question. A book, as most written materials are always page numbered. Often on a DB, the 'page' reference is filled up with a lot of characters which give multiple pages and/or special characters. One of my client (using FMP6) wishes to extract from that field a unique page number. He chooses to take the first valid number. All the following examples (take off the asteriscs) are supposed to give the 124 number as the first page : *124*; *(124)*; *124-126*; *124 et 125*; *124-126 et 200,201*; *124 et 200(?)*; *124, 125, 127-130*; *(?)124-126*; *124A et 124B*; ... I tried a formula approach using the 'substitute' function, but it often gave strange results. I suppose a better way would be to scan the 'page' field character by character, and disregard the non-numeric ones at least for the characters at the beginning. Then take the first number, first meaning that the search would stop when getting a non-numeric character. Say also as a limit that books pages never go beyond 4 digits. But I don't know how to specify a 'numeric character' in order to isolate it from the junk, nor recognize the 'non-numeric characters', nor setting up the whole system to achieve the task. Could you help me ? Many thanks. Remi-Noel |
#4
| |||
| |||
|
|
Thanks, Ursus, for your kind answer. But as I said, the client is still with FMP6, in which that 'Filter' function does not exist. Anyhow, even in FM9 it seems that is keeps a lot of numbers while I need only the first one, ie '124' alone. So, maybe, I'll have to take another approach. But I am glad your tried to solve my problem. Remi-Noel "Ursus" <ursus.kirk (AT) ziggo (DOT) nl> a écrit dans le message de news:28b4c$4b46fc03$535399fa$25478 (AT) cache2 (DOT) tilbu1.nb.home.nl... Remi-Noel I think the Filter function wil be helpfull. Filter ( MyField ; "1234567890- ," ) *124* returns 124 *(124)* returns 124 *124-126* returns 124-126 (take all characters left of the -) *124 et 125* returns 124 125 ( take all characters left of the space ) ; *124-126 et 200,20177*; returns 124-126 200,20177 (Take all characters left of the -) etc etc You will have to expand the string between the "" inside the calculation to suit your needs -- Keep well / Hou je goed Ursus "Remi-Noel Menegaux" <rnmenegaux (AT) free (DOT) fr> schreef in bericht news:4b46e3dc$0$30635$426a74cc (AT) news (DOT) free.fr... Hi, I wrote first my question in french on an english-speaking newgroup. Sorry. Anyway it was poorly phrased. So I go again with my question. A book, as most written materials are always page numbered. Often on a DB, the 'page' reference is filled up with a lot of characters which give multiple pages and/or special characters. One of my client (using FMP6) wishes to extract from that field a unique page number. He chooses to take the first valid number. All the following examples (take off the asteriscs) are supposed to give the 124 number as the first page : *124*; *(124)*; *124-126*; *124 et 125*; *124-126 et 200,201*; *124 et 200(?)*; *124, 125, 127-130*; *(?)124-126*; *124A et 124B*; ... I tried a formula approach using the 'substitute' function, but it often gave strange results. I suppose a better way would be to scan the 'page' field character by character, and disregard the non-numeric ones at least for the characters at the beginning. Then take the first number, first meaning that the search would stop when getting a non-numeric character. Say also as a limit that books pages never go beyond 4 digits. But I don't know how to specify a 'numeric character' in order to isolate it from the junk, nor recognize the 'non-numeric characters', nor setting up the whole system to achieve the task. Could you help me ? Many thanks. Remi-Noel |
#5
| |||
| |||
|
|
Hi, I finally got it done, using the pseudocode "FirstWord(MyField; 1)", with something a little more complicated for the comma (,) case. Thanks again. Remi-Noel |
character,then you could use the Position function to work out where the first
semi-colon is, and then use the Left function to copy all the characters up
until that position.
e.g.
FirstPageNumber Calculation, Text Result, Unstored
= Left(MyField; Position(MyField; ";"; 1; 1) - 1)
This will work for pages numbers which have non-numeric characters like
commas, hyphens, etc. in them.
Helpfull Harry
)
#6
| |||
| |||
|
|
"Remi-Noel Menegaux" <rnmenegaux (AT) free (DOT) fr> wrote in message news:4b4747e2$0$10087$426a74cc (AT) news (DOT) free.fr... Hi, I finally got it done, using the pseudocode "FirstWord(MyField; 1)", with something a little more complicated for the comma (,) case. Thanks again. Remi-Noel If the pages numbers are always separated by the semi-colon ( character,then you could use the Position function to work out where the first semi-colon is, and then use the Left function to copy all the characters up until that position. e.g. FirstPageNumber Calculation, Text Result, Unstored = Left(MyField; Position(MyField; ";"; 1; 1) - 1) This will work for pages numbers which have non-numeric characters like commas, hyphens, etc. in them. Helpfull Harry ) |
#7
| |||
| |||
|
|
Thanks, Harry, for your suggestion. In fact, I said earlier that pseudocode "FirstWord(MyField; 1)" was working, as I didn't remember the english name of the Left function. So you should read that Left(MyField; 1) works on most cases (as the examples I gave). Thanks for your contribution. Remi-Noel |
the Position function).
LeftWords would be the English name for the function you're using above, but
may have problems with things like page ranges. e.g. "2 -15" would be three
words.
Helpfull Harry
)
#8
| |||
| |||
|
|
"Remi-Noel Menegaux" <rnmenegaux (AT) free (DOT) fr> wrote in message news:4b47f911$0$7637$426a74cc (AT) news (DOT) free.fr... Thanks, Harry, for your suggestion. In fact, I said earlier that pseudocode "FirstWord(MyField; 1)" was working, as I didn't remember the english name of the Left function. So you should read that Left(MyField; 1) works on most cases (as the examples I gave). Thanks for your contribution. Remi-Noel The Left function only takes individual characters (which is why you need the Position function). LeftWords would be the English name for the function you're using above, but may have problems with things like page ranges. e.g. "2 -15" would be three words. Helpfull Harry ) |
#9
| |||
| |||
|
|
Thanks for correcting me : I really meant, as you said, LeftWords(MyField; 1). It does what I was looking for, ie only the first single 'word' (the first number). In the example you give '2 -15' would then give 2 alone, which is what I want. (it is still a simplication, as if the separator is a comma - like 2,15 - then the above formula will give '2,15', when I need '2' : so I added afor the first character after the '2' to be a comma, then I forget the rest) |
haven't used it since I finished school, back when dinosaurs roamed the
Earth).
Helpfull Harry
)
#10
| |||
| |||
|
|
"Remi-Noel Menegaux" <rnmenegaux (AT) free (DOT) fr> wrote in message news:4b48310d$0$7649$426a74cc (AT) news (DOT) free.fr... Thanks for correcting me : I really meant, as you said, LeftWords(MyField; 1). It does what I was looking for, ie only the first single 'word' (the first number). In the example you give '2 -15' would then give 2 alone, which is what I want. (it is still a simplication, as if the separator is a comma - like 2,15 - then the above formula will give '2,15', when I need '2' : so I added when the first character after the '2' is a comma, to forget the rest) Your English is a billion times better than my extremely rusty French (I haven't used it since I finished school, back when dinosaurs roamed the Earth). Helpfull Harry ) |
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.