Splitting names that have variable numbers of spaces 1

[Brian S]

Hello,
I have been searching through posts on splitting name fields and have been able to kind of put a few things together but nothing seems to fit all of my scenarios. I have a Name field that may or may not contain spaces and if it does the number of spaces may change. I want to take just the first word before a space and turn that into a First Name field, the rest of the data after the first space (if any) goes to the last name field. If there are no spaces then the data is just duplicated in both fields. Examples:

Data:
John Smith
John
J J Smith

Output Firstname:
John
John
J

Output Lastname:
Smith
John
J Smith

Thank you in advance for any advice on this.

 

[pmax9999]

Hi

I don't have access to Crystal Reports at the moment to validate the syntax of these formulas, but I think the following will work:

First Name

SPLIT({Table.NameField}, ' ')[1]

Last Name

TRIM(REPLACE({Table.NameField}, SPLIT({Table.NameField}, ' ')[1], ''))

Hope this helps.

Cheers, Pete

 

[pmax9999]

Actually, the Last Name formula will not work for the cases where just one name has been entered. You will therefore need to add a test to the Last Name formula to check for a space, and where one does not exist, return the field value.

It will be a day or 2 before I get access to a machine with Crystal installed, but let us know if you need further assistance and if someone else doesn't jump in with a solution I will take a look when I can.

Pete.

 

[pmax9999]

I've now had an opportunity to test my approach and the Last Name formula did not work for the example where the two initials were the same (ie, "J J Smith").

The following amendment for the Last Name formula does work for all examples provided, but of course does need to be tested more widely.

Last Name

WhilePrintingRecords;

Local NumberVar l := LEN(SPLIT({Table.Name}, ' ')[1]);

IF      UBOUND(SPLIT({Table.Name}, ' ')) > 1
THEN    MID({Table.Name}, (l + 2))
ELSE    {Table.Name}

For the benefit of other people who may be looking for a solution to a similar problem, please respond lettting us know if this helps you or not.

Cheers, Pete.

 

[pmax9999]

If you are not going to acknowledge the assistance and provide feedback on whether it worked or not, I'd suggest you might struggle to get further assistance in the future if you need it.

 

[Brian S]

I appreciate the suggestions, Pete apologies for the delay I was out sick for a few days. I was able to come up with a workaround using a number of different suggestions. I know what I have is not pretty but it works for our use case where again the first word is the first name and everything else goes to last name.

I built a first name formula @1
stringVar array Names := Split ({CustomerMaster.CustomerName}," ");
Names[1]

Then built formulas @2 - @6 incrementing each one
stringVar array Names := Split ({CustomerMaster.CustomerName}," ");
If UBound(Names) > 1 Then
Left(Names[2], 20)
Else
{@1}

Then for the last name formula @FullLastName I add 2-6 together:
{@2}+" "+{@3}+" "+{@4}+" "+{@5}+" "+{@6}

Again I know it is not pretty or maybe the best way to do it but for our use it does accomplish what we need. Hopefully this helps someone else out.