Jump to content

Determining characters or numerals in a text field


This topic is 7289 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 mad.gif and (I think) MSWord mad.gif, 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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

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

Paul

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 7289 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.