Use split() to split a non standard string

[kate8]

Hi All,

I am working on a report which has some non standards data. Suppose I should put out everyone’s first name, last name and middle name, but for some people, they have these three fields empty, then I need to get their name from the full name field. Since it is required to put comma in between, that is why I am using the separate fields. But the problem is that I don’t know in the full name filed how many string it will be. It could be only one, like a company’s name,like "AutoOne", it could be two or three, like "Mary Line", "Mike J Stone". If it is one, I’ll use it as last name, if it is two, I’ll use first one as first name, second one as last name…etc. I try to use split(), but can’t make it work.
Do you have any idea how to make it work?
Thanks a lot for any help!!!

 

[lbass]

This isn't very clear. You are starting with the names all in one field and you want them in one field with a different display? Or you want them in three different fields? Please show some sample data of how the data currently looks and then how you want it to display.

-LB

 

[kate8]

Hi LB,

I need display three fields in the report:
first name,middle name,last name,
In the database, we have first name, middle name, last name field as well. It is very simple if all the data are standard, I can just put all three fields out and add comma in between. But now for some records, their three fields have no data. So I have to use another field called "FullName". Now I need to split the FullName into three parts if it has three. Like the example in my first post, that field could be "AutoOne" or "Mary Line" Or "Mike J Stone". When I split that FullName string, I don't know if it is "AutoOne" Or it is "Mike J Stone". If it is "AutoOne" I will put the whole string under last name, if it "Mike J Stone", I'll use Mike as first name, J as middle, Stone as last name.
How can I do that? I hope I explain it clear.

Very appreciate your respond!!

 

[Madawc]

Use 'ubound' to find how many parts the string has, in three separate formula fields for the three name parts. So if it is 1, leave first name and middle name blank and take the name as last name. If it is 2, take item 1 for first name, leave middle name blank and and item 2 for the last name. And so on.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[kate8]

Thanks Madawc!!

I am trying this. Since I still need to use those first name, middle name, last name fields. Only do the split when those fields have no data. So when I do this for formula LastName, I got error"Thematching} for this field name is missing". I can't use "if then" either. Any Idea? Thanks a lot!!

while (isnull({LAST_NAME}) or({LAST_NAME})= "" ) do
{
if ubound(split({INDIVIDUAL.D1NAME}))=2 then
split({FULLNAME}," ")[2]
}
else
{
{LAST_NAME}
}

 

[kate8]

I have made it work by putting all condition under one if.

Thanks a lot all the helps!!!