How to get name in right order

[#1]

Hi all,

I have a field called NameVZ which looks like this :    F.M.J. Hof van den

where F.M.J. are the initials, Hof is the surname and "van den" the preposition.

I'd like to create two fields out of this. One with the Initials which is easy ->  SubField(NameVZ, ' ',1) as Initials

The other field should have preposition + Surname in one:    "van den Hof"

But how do I do that as the propostion can be different for each name?

So to be clearer:

Field NameVZ   (examples)

F.M.J. Hof van den

T. Johnson

S. Haaren van

B. Beuker de

should become

Field FullnameVZ

van den Hof

Johnson

van Haaren

de Beuker

Preposition before the surname

[Anonymous]

There are many ways.  Try this for example

=textbetween(NameVZ, ' ', '')

[Anonymous]

You could do a right() on the name, using the width of the intials that you've already detected.

Something like this:

right(NameVZ,Len(NameVZ)-SubField(NameVZ, ' ',1))

Jonathan

[#4]

Hi Jonathan,

Don't think that works as this gives me "Hof van den"  as where I would like to have "van den Hof"

[#5]

Hi Michael,

Can you clarify this a bit, as I don't understand how this works in my situation.

[Anonymous]

The textbetween(text, s1,s2) returns substring of the "text" field value between text s1 and text s2.  In your situation, I used s1 = ' ' (one space), and s2='' (nothing).  So, the result wil be from the character after the first space and to the end.

Regards,

Michael

[Anonymous]

Another option is to use mid():

=mid(NameVZ, index(NameVZ, ' ')+1)

Here I calculate the position of the 1st space, add 1 to get to the position of the first character after space, and use mid() to cut from that position to the end.

[Anonymous]

Sounds like you want to set up a specific processing rule should you find the existence of "van den", and that this is at the very end of the string.

In that case, something like this may do it:

SET EndName = textbetween(NameVZ, ' ', '');

if(Lower(Right(NameVZ,7)='van den',

   'Van den' & textbetween(EndName, ' ', ''),

   textbetween(NameVZ, ' ', '')

)

I think I prefer Michael's texttbetween() function to the right(), mid() etc string chopping functions. Guess it's the thing I've learnt from the forum today.

Jonathan

[CELAMBARASAN]

Hi,

You can make use of it

LOAD *, Trim(Mid(Trim(Mid(Text, Len(Initial)+1)), Index(Trim(Mid(Text, Len(Initial)+1)), ' ')) & ' ' & SubField(Trim(Mid(Text, Len(Initial)+1)),' ', 1)) AS Name;

LOAD *, Left(Text, index(Text, '.', -1)) AS Initial  Inline [

Text

F.M.J. Hof van den

G.N.K. Ipg wbo efo

H.O.L. mqh xap fgp];

[#10]

No, "van den" is just a possibility. In other names it can be blank or have other options like "van" "van de" "de" "den" etc..

So searching on text is no option.