How to 'parse' data from a text field

[reitter]

I have a field pp.name (last name,first name,mi)which will return data like:

Mouse,Mickey A.

Of course the names are all variable lengths but they are separated by a comma.

What I need to do is make 3 separate fields:

Last name, First Name and Middle Initial

I can't seem to locate any formula that will let me strip to the ',' any ideas??

 

[IdoMillet]

Use the Split() function.

Split(pp.name, ",&quot[1]
should give you the first portion before the comma.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

You can use the split function as follows:

stringvar lastname := split({pp.name},",&quot[1];

stringvar firstname := split(trim(split({pp.name},",&quot[2])," &quot[1];

stringvar midinit := split(trim(split({pp.name},",&quot[2])," &quot[2];

Or, you can use the following:

stringvar lastname := left({pp.name}, instr({pp.name},",&quot-1);

stringvar firstname := trim(mid({pp.name}, instr({pp.name},",&quot+1, instrrev({pp.name}," &quot-instr({pp.name},",&quot));

stringvar middleinit := right({pp.name},2);

-LB

 

[reitter]

Thanks for the help, either of the options is working for the last name and first name. However the darn middle initial is driving me crazy. Now that I've been able to separate the first name out the original data looking like

Micky,Mouse A. or
Micky,Mouse A or
Micky,Mouse

The formula stringvar middleinit := right({pp.name},2) is giving me back:

A. or
A or
se

What I really need is to return only the A or leave it blank if nothing is after the space???


Thanks for all of the help, I'm way ahead of where I was.

 

[lbass]

I'm not where I can test this, but try the following:
Add "whileprintingrecords" to the beginning of the formula that defines all of the elements, and then for the middle initial, try something like:

if {pp.name} > len(lastname)+len(firstname)+1 then
middleinit := trim(right({pp.name},2) else
middleinit := "";

//where the "1" is for the comma. If there is a space after the comma, make it "2"

This middleinit definition should follow the definitions of the other two elements. Again, not sure this will work, but something like this should.

-LB

 

[lbass]

I don't know if you resolved this already, but I fooled around with this some more and the following seems to work. This assumes that you want middle initials represented without the period.

First, create a formula {@name}using your name field, so that there is always a trailing space:

{pp.name} + space(1)

Then use the following to define each component of the name:

stringvar lastname := left({@name}, instr({@name},",&quot-1);

stringvar firstname := trim(mid({@name},instr({@name},",&quot+1,(instr(instr({@name},",&quot+1,{@name}," &quot-instr({@name},",&quot-1)));

stringvar middleinit;
if len({@name}) > len(lastname + firstname)+3 then middleinit := trim(replace(right({@name},3),"."," &quot) else
middleinit := "";

-LB