I would like to create a calculation field that removes all Alphanumeric letters from the end of a specified field.

Example...

Current Field

12-12-34A

43-65-87C

23-43-433G

12-23-765

12-32-54R

The results would look like this...

New Calc Field

12-12-34

43-65-87

23-43-433

12-23-765

12-32-54

This seems like it would be easy but for the life of me I can't figure it out! Any help would be much appreciated, thanks!

If the hypens must stay put, then I believe you are looking at a script or calculation that would have 26 Substitute steps.

Substitute(Substitute(Substitute(Substitute(Substitute('Current Field" "A", ""),

"B",""),

"C",""),

"D",""),

"E "")

Etc.

HTH

Lee

By the way, FM7 has a new function called "Filter" that can remove all chars not listed, so:

So?

They are using 6, so why even mention it?

Believe it or not, not everyone is planning to move down to v7.

Lee

There are many who have moved up to 7. I was unaware of Filter() and found the information VERY useful. We share the same problems but in 7 we have different solutions available. This is invaluable input to many people on this forum. I am pleased the different solutions are mentioned, just as I try to mention how something would be solved if one were using prior versions when responding to a 7 question.

And for those considering the move up ... these kinds of additional features may help them with their decision.

If there's only one letter possible at the end, then Left( field, Length(field) - not PatternCount( "0123456789", Right( field, 1 ) ) ) also works.

Thank you JT.

I learned from your calculation also. It appears simpler than multiple Substitute()s. Nesting of Substitute() is no longer used in 7 but I will be designing in multiple versions of FileMaker, as will most of us. And the logic of good calculations spans all versions.

I am very good at calculations except those that involve text, numbers or dates. So I will not limit my learning ... forward or back.

Lee Smith said:

So? They are using 6, so why even mention it?

They'll see the light eventually.

Others have mentioned to me that they are interested in learning about 7, even if they don't have it yet. I thought I'd point out the new, more efficient method for those who have already taken the plunge - god knows they have suffered enough, the poor sobs.

Was your example really about ONE field (that is, it includes returns) or does each line indicate a different record and you were just showing us several records worth of example data? Because if each line is a record then the calc would be:

leftwords(source,2) & "-" & textToNum(rightwords(source,1))

Thank you for all your responses. the "Left( field, Length(field) - not PatternCount( "0123456789", Right( field, 1 ) ) )" seems to work the best. Thanks!