Manipulating field data

[ann_bas]

Hi,

I have a field where I have first name and last combined but a comma in between them like Smith,John. I have over 70,000 names. So I can't manually separate these first name & last name. Is there a way I can separate these first name & last name? Any help would be greatly appreciated!

Edited April 23, 2009 by Guest
The Topic: <a href="http://fmforums.com/forum/showtopic.php?tid/202928">Manipulating field data</a> was moved from <a href="http://fmforums.com/forum/showforum.php?fid/34">Value Lists</a> to <a href="http://fmforums.com/forum/showforum.php?fid/31">Calcul

[mr_vodka]

Create two field name_last and name_first.

Then do the following:

Show All Records

Click into field name_last

Replace field contents [ name_last; GetValue ( Substitute ( YourCurrentNameField; ","; ¶ ); 1 ) ]

Click into field name_first

Replace field contents [ name_first; GetValue ( Substitute ( YourCurrentNameField; ","; ¶ ); 2 ) ]

[Lee Smith]

Hi John,

I'm I misreading this need?

Why use the Replace, these would be new calculation fields, and you would be leaving the FullName field intact..

Lee

[mr_vodka]

Perhaps I misunderstood the needs here. Yes she can create a calc but under normal circumstances, the first and last names should really be broken out to their own separate fields anyway. Just my cents of course. :o

[ann_bas]

Hi John,

I tried this and I got 0 value in both first and last name fields.

[Lee Smith]

Here are two calculations that I got from comment.

Calc for First Name

Right ( FullName ; Length ( FullName ) - Position ( FullName ; "," ; 1 ; 1 ) )

Calc for Last Name

Left ( FullName ; Position ( FullName ; "," ; 1 ; 1 ) - 1 )

Note, the FullName field is your name where the data is now.

HTH

Lee

[ann_bas]

Thanks Lee. It worked!

[Fitch]

Oops, I posted the same formula John did.

Edited April 23, 2009 by Guest
It should work - make sure result is of type text, not number.