asked on

Need to extract the first name

I need to extract the first name only.

Here is the current format from the system:

LastName;Firstname Middle Initial
Ex. Doe;Jane R

I am able to extract the first name with the middle initial. I just need the first name only.

I am using a two-part formula

Part 1: right({PRM_MASTER__EMPLOYEE.Employee_Name},len({PRM_MASTER__EMPLOYEE.Employee_Name})-InstrRev({PRM_MASTER__EMPLOYEE.Employee_Name},";"))

Part 2: left({@Formula 1},len({@Formula 1})+1)

Bill Prew

You could do it all at once with a regular expression extract, like:

regexp_extract({PRM_MASTER__EMPLOYEE.Employee_Name}, '^[^;]+;(\S+)', 1)

Open in new window

»bp

Lani Vriezema

ASKER

Bill,

The formula did not work in Crystal report.

Best Regards,

Mailan Vriezema

Bill Prew

You had marked this question as topic "R", is that not the case? Is it actually a Crystal Reports question? If so please adjust the topics to indicate this...

»bp

Lani Vriezema

ASKER

Bill,

My apologies.

L

Bill Prew

Doing it all at once, try this.

Mid({PRM_MASTER__EMPLOYEE.Employee_Name}, Instr({PRM_MASTER__EMPLOYEE.Employee_Name}, ";") + 1, Instr(Instr({PRM_MASTER__EMPLOYEE.Employee_Name}, ";"), {PRM_MASTER__EMPLOYEE.Employee_Name}, " ") - Instr({PRM_MASTER__EMPLOYEE.Employee_Name}, ";") - 1)

Open in new window

»bp

Lani Vriezema

ASKER

I got an error message:

String length is less than 0 or not an integer.

Bill Prew

Could you have data that has no middle initial? For example:

Smith;Joe

»bp

Lani Vriezema

ASKER

Some employee files do not have a middle initial.