Determining characters or numerals in a text field

[Damocles]

In a previous (unrelated) post, Fitch said:

The next (tricky) part is checking that each character is a member of the set:

abcdefghijklmnopqrstuvwxyz1234567890.@

I've run into this problem recently, and hope there is a solution that I am just missing. In parsing some data, I wanted to get the position of the first and last numerals in the text field, but could not find the conventional method of telling FM to check to see if the character was a letter or a number. In MSExcel and (I think) MSWord , it's possible to find "the next numeral" on the page.

Here's my question, then:

Is there a way (short of Case or If statements for all the possible occurrences) to find the position of the first numeral or letter in a field?

I know I can create a number field and insert the text into the number field, then go back and find the position of the resulting contents of the number field, but that seems fairly roundabout. What I'd like to be able to do is:

Position(TextField,^#,1,1) to find the first numeral (any number) in the TextField or

Position(TextField,^@,1,1) to find the first letter (any letter) in the TextField.

Am I just missing a basic concept here? Is this already a FM function?

Thanks!

Paul

FileMaker Version: 6

Platform: Windows XP

[-Queue-]

Here's a method to retrieve the first number or letter in a text field, using repeating calculations and a value list.

There is a script required, but only to 'refresh' the data by going to Find Mode, then back to browse mode, when the preference of first letter or number is changed.

Damocles_first_number_letter.zip

[Damocles]

Thanks,

I'm working to see what your example does, although it does answer the original question nicely. Apparently, there is no built-in FM function that will find the first letter/number...

(Unless you happen to have Veteran status, and know a few tricks!)

Paul

[-Queue-]

Essentially, all it does is extract each character of the text field (up to 50 characters in this case) into its own repetition, then compares each to either the alphabet or 0-9, depending upon whether Letter or Number is selected. The boolean result of the comparison is then used as a value list, sorted by the index (the repetition number). Then the Pos field uses this value list, removes all carriage returns, and returns the position of the first 1 (true), which corresponds to the position of the related character in the text field.

So, as Pooh said, it goes around a long distance to come back a short distance correctly...

If you need to test more than 50 characters, you would increase the number of repetitions for all repeating fields, add the additional numbers to the gIndex field, put the _ (constant of 1) field on the layout, and perform a relookup on it.

[-Queue-]

Note: This technique, and many others like it, have been obliterated in version 7, with no foreseeable workarounds to the workarounds.

[Damocles]

Smith-Jones Mary 2 Main Street Springfield Democrat

Flintstone Wilma 100 Elm Springfield City Unenrolled

Flintstone Fred 100 Elm Street Springfield Unenrolled

The above examples were part of an issue I was playing with earlier this week. I was trying to find the position of the numerals in order to parse the data into separate fields. Ultimately, it was not necessary, but I did discover the problem we have been discussing.

I was also hoping to be able to extract the position of punctuation marks (, : ;) within similarly concatenated text. (and hoping that there would be a streamlined, stock solution!)

Thanks for the solution.

Paul

[Ugo DI LUCA]

Hi,

I didn't tested it on your examples, but.

Position(YourText, Left(TextToNum(Substitute(YourText,"0","1")),1),1,1)

should give you the first numeric position

while

Position(YourText, Right(TextToNum(Substitute(YourText,"0","1")),1),1,1)

should give you the last numeric position

FileMaker Version: 6

Platform: Mac OS 9

[Ugo DI LUCA]

Actually, the first should be modified to :

Position(YourText, Left(Abs(TextToNum(Substitute(YourText,"0","1"))),1),1,1)

in order to disregard the possible "-" in your string.

FileMaker Version: 6

Platform: Mac OS 9

[Ugo DI LUCA]

While the second, wouldn't require the Substitute trick (which is only here to account for leading "0") at all...

Position(YourText,Right(TextToNum(YourText),1),1,Patterncount(YourText,Right(TextToNum(YourText),1)))

Would these work.

FileMaker Version: 6

Platform: Mac OS 9

[Damocles]

I checked, and here's the resulting file.

Ugo's solution works great except for a couple things. If the last number is used more than once in a string, the field reports back the position of the first occurrence of the number.

For Example:

abcd1234efgh Will return LeftPos:5 RightPos:8 (Works fine)

abcd1212efgh Will return LeftPos:5 (Good) RightPos:6 since that is the first occurrence of the "2" in the string.

abcd4444efgh Returns LeftPos:5, RightPos:5.

Also, this solution did weird things when the text string has zeroes in it.

I'll look at to see if I can do the mental acrobatics that make it work, but would also appreciate any other advice you have.

Thanks!

Paul

FileMaker Version: 6

Platform: Windows XP

Number Position.zip

[Damocles]

Sorry, you solved the problem while I was checking the original solution. The newest post works great!! Thanks Paul

[-Queue-]

Very nice, but how would you do the same thing for letters?

FileMaker Version: 5

Platform: Windows 2000

[Ugo DI LUCA]

Hi Queue,

You mean if this string starts with a "'" or a "-" or a "*" ?

Well, I'm unsure if this feature is equal for each version, but the MiddleWords() should trim any leading or ending special characters.

So, if you substitute the numeric values, then the MiddleWords should just return the first Letter or last one with :

FirstLetter Position :

Position(

Left(

MiddleWords(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(YourText,

"0","*"),

"1","*"),

"2","*"),

"3","*"),

"4","*"),

"5","*"),

"6","*"),

"7","*"),

"8","*"),

"9","*"),

WordCount(YourText)),1),1,1)

As any special characters included within the String would still be included, then, adding the length of the "Parsed MiddleWords" would just work.

LastLetter Position = FirstLetterPosition+ Length(MiddleWords(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(YourText,

"0","*"),

"1","*"),

"2","*"),

"3","*"),

"4","*"),

"5","*"),

"6","*"),

"7","*"),

"8","*"),

"9","*"),

WordCount(YourText))

Agreed it's a bit tedious though.

FileMaker Version: 6

Platform: Mac OS 9