8 Replies Latest reply: May 15, 2014 10:08 AM by Nick Osdale-Popa

formula question

is there a way to use monarch to create a calculated field which would take

the last 8 digits and print the 8 only when there is 8 digits, if the following item below is not 8 digits, then print blank?

I know how to do the Left function to show the 8 digits, but want to depict if the item does not have a valid account ie:

Valid account:  10096 RR SBLWA-1219880071     (available with last 8 digits

Invalid Account:  34797   (does not have 8 digits available)

• formula question

There may well be other ways to accomplish this, but here is one:

If(IsNull(Val(Right(FieldName,8))=1),"",Right(FieldName,8))

It checks the eight digits to the right and makes sure that they are numeric.  If they are numeric they are populated in the calculated field; otherwise it is blank.

• formula question

Hi Joe

unfortunately it does not do that

it is basically doing the same thing as my Right(,8)

Sample data below

please let me know if you have any ideas to get rid of the noise only the first item below appears to be valid, the rest are not and should be blank

09870400

24 00071

04 00042

98 00071

MARKETS

MARKETS

91255

• formula question

I've got about five minutes before I "go offline" for the day, but try this out:

If(Len(RSplit(Account,2," ",1))<8,"",If(IsNull(Val(RSplit(Account,2," ",1))),"",RSplit(Account,2," ",1)))[/CODE]

• formula question

Thanks Data for trying

don't think that is working as it gives me the following,

removing blanks and the whole thing, i need basically to display the last 8 digits, and if they are numeric then print it, and if not, then print blank

-000800109870400

000WA-1509874801

SBLWA-1219880369

MGTCBEBEECL

SBLWA-1210019535

SBLWA-1219880073

SBLWA-1219880071

000WA-1209874801

000GA-6009872354

2T9HE-0109876000

BSDTUS33

//XX629132

BSDTUS33

BSDTUS33

BSDTUS33

000WA-2009874801

10026827

• formula question

This first removes any alpha character or space and then tests the length of the remaining field.  If it is not 8 characters long, the field is a blank; otherwise, it is FieldName.

If(Len(Strip(FieldName,"abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ"))<>8,"",FieldName)

• formula question

Hello Adonis, hello everyone,

I think Joe´s approach is definitely right, but in case the data contains rogue lines like:

1234-5678

123//45678

12 345678[/CODE]

and the intention is to exclude[/B] these, then we need to rsplit before we strip the field. I´m assuming that the separators are only space, / and -, but if others are used this could be extended.

Perhaps it´s easier to split this into three stages - firstly define a calculated field containing the right hand side of the field, broken on the possible separators. If there are no separator characters, then the whole field will be returned:

=rsplit(rsplit(rsplit(;2;" ",1);2;"/";1);2;"-";1)

Now we can make a new calculated field by removing from the field of all its non-numeric characters:

=strip(;strip(;"01234546789"))

Finally, we can make which is the account number where this field has exactly eight characters, and is "Invalid" in other cases:

=if(len()=8;[stripped];"Invalid")

You can then filter out all lines where ="Invalid" easily.

Best wishes,

Olly

• formula question

First, check that the Right 8 characters have a length of 8. Then, strip out all numbers. If the length is 0, then you have 8 digits:

IF(LEN(RIGHT(FIELD,8))=8,IF(LEN(STRIP(RIGHT(Field,8),"0123456789"))=0,RIGHT(FIELD,8),""),"")[/quote]