Hi all

My company has a lots of Excel spreadsheet that has hyphen separateing information in the same cell.

Example:

Adrian-Jude-Walker

Shawn-DeRouen-Delcambre

We have converted the Excel spreadsheets to a FM Database.

We have created four fields :

Name global field

First name auto enter calculation field

Middle name auto enter calculation field

Last name auto enter calculation field

We import the data into the global name field and would like to removed the hyphen and place the first name of each person in the first name field and do that to the middle name and the last name.

I created two variables to use in the LET() function

one called _Extractline

the other one is called _nameValues

This is what I have so far but something is not right and I am having trouble finding it.

Let(

[

_Extractline=LeftValues(name;1);

_nameValues=Substitute(_extractline;"-;paragrah reurn);

];

GetValue(_nameValues;1)

I have attached a file of what I am trying to accomplish.

Thanks

Lionel

name.zip

You can split name by following calculations

First Name : leftwords(name;1)

Middle Name : middlewords(name;2;1)

Last Name : middlewords(name;3;1)

[color:red]Opps, I forgot to say that I would change the field structures. You could create a new field and call it [color:blue]Full Name, and use it in place of [color:blue] Frist_name. then change the existing fields so that these calculations will populate them. Here is a calculation that you can use for the first name, and then modify it for the other two.

However, just looking at your data, I'm concerned with the names, as this will be a problem with those that don't have a middle name. i.e. the Middle name will become the last name.

Let ( [

n = 1 ;

t = "-" & [color:blue]Frist_name & "-" ; /*If you change the fields as suggested, change this one too*/

start = Position ( t ; "-" ; 1 ; n ) + 1 ;

end = Position ( t ; "-" ; 1 ; n + 1 )

] ;

Trim( Middle ( t ; start ; end - start ))

)

change the [color:red]1 to 2 and 3.

I like to credit the donor when I can, but I forgot to not who contributed this one. I believe it was comment, but if it was some one else, maybe they can take a bow.

HTH

Lee

Edited November 30, 200718 yr by Guest

Your method will work, but again, you have to know the data. If you have a split, multiple names, or hyphenated last name such as [color:blue]Van Dyke, or [color:blue]Van-Dyke , you will have some troubling results. I.e. using your method, you would get the [color:blue]Dyke, and using my suggestion, you would get the whole last name, unless it is hypenated, int which case you would get the [color:blue]Van.

The best method I have seen for this, is in an v3/4 file that was done by Lynn Bradford, that I attached Link

HTH

Lee

Lee

Thanks and I will try the calc and let you know how I make out, but I do not quiet follow you about the full name field.

What I want and need to do is create one record for each entry in the global field. A script will create a new record and the auto enter calc will populate the fields from the first record in the global field and then delete that entry. Then so on until the global field is empty. The data structure is First middle and last name. We will not use the spreadsheets again once we convert all the data from the spreadsheets.

Thanks

Lionel

I'm going to make a small mock up of this, to be sure we are on the same page.

In the mean time, take a look at the file I linked above.

Lee

Lee

I supplied a copy of what I want to do.

Would you please look at that

Thanks

Lionel

I saw it, but maybe this will help explain my point, and help you at the same time.

The_Big_Bear.fp7.zip

Edited December 1, 200718 yr by Guest

Lee

Thanks but there is no file

Lionel

******* Gremlins.

:giggle:

Lee

Thanks

I will look the file over and get back

but thanks again

Lionel

Lee

Thanks for the calc. Works really well.

I have a question, about the calc content.

I assume that the "n" and the "t" are variables.

How did you assign a variable in a calculation with out defining it in the dialog box?

Thanks

Lionel