Jump to content

Removing last alphanumeric digit from field


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

Recommended Posts

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!

Link to comment
Share on other sites

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

smile.gif

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Thank you JT. wink.gif

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. crazy.gifgrin.gif So I will not limit my learning ... forward or back.

Link to comment
Share on other sites

Lee Smith said:

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

They'll see the light eventually. wink.gif

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6431 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
 Share

×
×
  • Create New...

Important Information

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