4 Replies Latest reply: May 15, 2014 9:55 AM by Oscar _

# Calculated Fields

I have a list of credit card numbers that I want to use to create a new field.  The field I want to create would have cc numbers beginning with 4 return as CC-VM, beginning with 3 as CC-AM.  I have tried using the a lookup calculated field with no luck.  Can anyone help?

• ###### Calculated Fields

Hi Oscar and welcome.

If the CC Number field is Character type

left(ltrim(),1)

Should give you the first character for your look up table. LTRIM may not be required but often is.

If it is NUMERIC

left(ltrim(str()),1)

should give you the first character in a new Character type calculated field from which you can do the lookup.

(Or of course you can simple use the appropriate formula when you define the lookup.)

Given the lookup will be quite constant over time an intrnal lookup table would seem like the best solution

An alternative is to use an IF function to calculate the field. For example;

IF(left(ltrim(),1)="3","CC-AM",IF(left(ltrim(),1)="4","CC-VM", "Other")

Should give CC-AM if the number starts with3, CC-VM if itr starts with 4 or "Other" if it is neither 3 nor 4. (Assuming I typed the formula right!)

HTH.

Grant

• ###### Calculated Fields

When I apply the formula it says "Invalid Delimeter"

• ###### Calculated Fields

Originally posted by Oscar:

When I apply the formula it says "Invalid Delimeter" /b[/quote]Which formula?

Edit: The IF based formula needs another ) on the end.

IF(left(ltrim(),1)="3","CC-AM",IF(left(ltrim(),1)="4","CC-VM", "Other"))

I assume that was the one you had a problem with?

Grant

• ###### Calculated Fields

Thanks! It worked like a charm.