Seeking better alphanum string parsing for LC#

[ESpringer]

Perhaps someone can save me some grunt-work in figuring this out.

Background: Library of Congress catalog call numbers alternate back and forth between letters and numbers, like this: "E185.97.L79P48" or "BJ1012.E8957" or simply "B2558" (And some folks append the copyright year at the end).

There is little about the lengths that can be trusted. If there is a letter after the initial aphanumeric pairing, there's a period before it, *but* the first period may also serve as a decimal in the first numeric string.

Task: My current field (with one-line string as above) needs to have a calc variant for book spine labels that inserts carriage returns like this:

E

185.97

.L79

P48

or

BJ

1012

.E8957

In other words, I need FileMaker to insert carriage returns at the first, second, and fourth occasions on which the string switches back or forth between letters and numbers (preserving the period before the second alpha occurrence).

The ways I've tried with Filter and Position functions are imperfect, cumbersome, and seem inefficient. I wish I could use a wildcard for alpha and a wildcard for numeric, as in "get me, from LC# string, the position of: the first _alphabetic character_ after the first _numeric character_"!

Is this a custom function opportunity? Any flashes of insight? Thanks!

[Ugo DI LUCA]

Hi,

Not too used to Custom Functions, have a go with this massive substitute solution to see if it works with other load of data from your real files.

bookparse.zip

[The Shadow]

This seems to do the trick for your examples:

Splitter( toSplit; lastCh; splitCnt ) =

If ( not IsEmpty(toSplit);

Let( [ firstCh = Left(toSplit;1);

nextCh = If ( firstCh = "."; Left( toSplit; 2); firstCh );

switch = (not IsEmpty(lastCh)) and (IsEmpty(Filter(lastCh;"0123456789")) <> IsEmpty(Filter(nextCh;"0123456789")));

rest = Right(toSplit; Length(toSplit)- If(firstCh = "."; 2; 1))

];

If ( switch;

If ((splitCnt=1) or (splitCnt=2) or (splitCnt=4);

[ESpringer]

Ugo,

I'm blown away with appreciation that you would be willing to spell out such a complex calc on my behalf. I was on my way to trying to spell out that kind of thing, but felt sure it might be easier somehow... At any rate, your calc works perfectly (unlike my efforts so far), and can provide copy-paste help for similar alpha-numeric parsing. Thanks!

Shadow,

This is what I suspected was possible with a custom function. I'm still digesting how it works. I have a feeling that once I understand custom functions I'll find even more ways they could be useful. Thanks to FM7 for custom functions!

Long live those who *KNOW* and who are willing to share so generously!