Hi All,

I have a text field which contains basically random characters, alpha, numeric and symbol.

I need a calculation which returns characters only up to the first non-alpha character.

So:

"xyz2y" returns "xyz"

"y2r" returns "y"

"abc$tyu" returns "abc"

etc!

Another of those problems I expected to be easy, but have only come up with kludgy, long-winded solutions for.

Thanks...Sean!

I won't be the judge of the elegancy in the here suggested method, but it gets the matter done:

I would start out with a repeating calc'field with as many repetitions as required - containing this:

Lets Call it WhereBreak[30]

Case (

1 xor Case ( Length ( Extend ( theText ) ) > Get ( CalculationRepetitionNumber );

Position ( "abcdefghijklmnopqrstuvwxyz" ; Middle ( Extend ( theText ) ; Get ( CalculationRepetitionNumber ) ; 1 ); 1; 1 ));Get ( CalculationRepetitionNumber ))

Then let us make a Result calc'field containing this:

Left ( theText; Min ( WhereBreak )-1)

Enjoy!!!

--sd

Well it just occured to me that the break-char might be missing occationally .... hence:

Case (1 xor

Position ( "abcdefghijklmnopqrstuvwxyz" ; Middle ( Extend ( theText ); Get ( CalculationRepetitionNumber ) ; 1 ); 1; 1 );Get ( CalculationRepetitionNumber );1+Length(Extend ( theText )))

--sd

If you are willing to write out all the potential non-alpha characters, you could use:

Let ( [

chars = "1234567890!@#$%^&*~" ;

first = Left ( Filter ( text ; chars ) ; 1 ) ;

pos = Position ( text ; first ; 1 ; 1 )

] ;

Left ( text ; pos - 1 )

)

If that's not an option:

Let ( [

dirty = Substitute ( text ;

[ "a" ; "" ] ;

[ "b" ; "" ] ;

[ "c" ; "" ] ;

// ... continue substitution for all alpha chars ...

[ "X" ; "" ] ;

[ "Y" ; "" ] ;

[ "Z" ; "" ] ) ;

dirty1 = Left ( dirty ; 1 ) ;

pos = Position ( text ; dirty1 ; 1 ; 1 )

] ;

Left ( text ; pos - 1 )

)

Oops!

Should have specified this one is a FP6 question.

I'm happy to see it isn't that simple a trick!

Soren's option looks like it would work, but not as nice as I'd hoped

It means I would have to make a guess at the maximum number of characters in a field, and there will be a lot of repeat fields empty!

I'll keep thinking about this one...

In FM Developer 7, this would be a simple recursive calc. I can't actually write it at this moment, but here it is in pseudo-code:

LeadingText ( text ) =

If (the first character is a number ;

"" /* an empty string */ ;

Left ( text ; 1 ) & LeadingText ( all the characters in the original text except the first character )

Very simple, actually!! Give it a try. Recursion can be fun...

Oops indeed!

My second suggestion will work with prior versions - after you rewrite it to eliminate the Let() and nest the Substitutes.

Comment, you are a genious...

The following works a treat:

FirstAlphaCharacters=

Left( ALPHANUMERIC, Position( ALPHANUMERIC, Left( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Lower(ALPHANUMERIC ), "z", ""), "y", ""), "x", ""), "w", ""), "v", ""), "u", ""), "t", ""), "s", ""), "r", ""), "q", ""), "p", ""), "o", ""), "n", ""), "m", ""), "l", ""), "k", ""), "j", ""), "i", ""), "h", ""), "g", ""), "f", ""), "e", ""), "d", ""), "c", ""), "b", ""), "a", ""), 1), 1, 1)-1)

I doubt there is any better solution.

Thanks!

Good catch with the Lower() function. Missed that one completely. Some genius I am ...