Jump to content
Sign in to follow this  
The Big Bear

Calculation Help

Recommended Posts

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

Share this post


Link to post
Share on other sites

You can split name by following calculations

First Name : leftwords(name;1)

Middle Name : middlewords(name;2;1)

Last Name : middlewords(name;3;1)

Share this post


Link to post
Share on other sites

[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 by Guest

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

The_Big_Bear.fp7.zip

Edited by Guest

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.