Extracting Letters

[Slobey]

Hi everyone,

I am trying to create a field that will count only the letters in another field. I have tried different formulas to count them using getastext, filtervalues patterncount and substitute but can't figure it out. Basically if field a contains an alpha character (a-z) I want just the number of those characters to show up in field b. For example:

field a contains 11a24b

field b would show 2

field a contains Pend12

field b would show 4

Any ideas?

Thanks

Mike

[Slobey]

I got it.

Length(field1) - length(getasnumber;field1))

Unless someone has a more effecient calc.

Mike

[Slobey]

spoke too soon.

If field a starts with a zero, my formula counts it as text. so field b shows 1 it field a is 01562213. Still need help

Thanks

[comment]

How about:

Length ( YourField ) - Length ( Filter ( YourField ; "0123456789 " ) )

[Slobey]

That was it! Thanks

[sbg2]

What if the field contains a character that is not a number or or a letter? Might try...

Length (Filter(Lower (MyText); "abcdefghijklmnopqrstuvwxyz"))

[comment]

And what if it contains an accented or Greek letter?

[Slobey]

I am using this to find valid PO's in my database. The PO should only contain numbers, nothing else. So if I use Comments formula and subtract the numbers then if anything is left the PO is invalid. It doesn't matter what character is left. My calc is

case(

Length (PO field) - Length ( Filter ( PO field; "0123456789 " ) ) > 0 ; "Invalid";"Valid")

Seems to work for my purposes, or am I missing something?

[comment]

Not really. Except you could make it a bit simpler:

PO field = Filter ( PO field ; "0123456789 " )

The result could be a Number, formatted to show non-zeros as "Valid" and zeros as "Invalid".

[Slobey]

But that calc would show 061b493 as 061493 and call it valid, I want to flag everything that has a non numeric character anywhere in the field as invalid.

[comment]

But that calc would show 061b493 as 061493 and call it valid

No, it would return 0 (False) and call it "Invalid". Mind you, the formula is the entire line. IOW, the calculation field cValidPO =

PO field = Filter ( PO field ; "0123456789 " )