Remove text from end of field?

[aldipalo]

I am importing data from an excel spreadsheet. The first name field may or may not contain the middle initial. I have a calc in excel to parse the middle initial and put it into the proper cell, however, I can not delete the middle initial in the first name field, because that nullifies the calc in the middle initial cell. Also, in FM I am using the "UniversalCapitalizer" CF. So, can I add a case function to remove the middle initial, if there is one, from the First name field?

The data comes in as:

John P.

Mary J.

Joseph

Argyle S.

etc.

Your help is appreciated.

Thanks,

Al

[mr_vodka]

Hi Al,

I am not familiar with that CF but just some thoughts.

Can you not have another calc field in Access that only gets the first name and import that?

How will you distinguish between a middle name and a last name with a space involved such as Van Winkle ?

*Edit - I misread your OP. I thought you had said middle name, not middle initial.

Edited March 15, 2007 by Guest

[comment]

How about:

Case ( Right ( FirstName ; 1 ) = "." ; LeftWords ( FirstName ; WordCount ( FirstName ) - 1 ) ; FirstName )

[aldipalo]

Hi Comment:

Ok so I added your calc under the CF statement:

universalCapitalizer (First_Name ;"First_Name" )

Case ( Right ( First_Name ; 1 ) = "." ; LeftWords ( First_Name ; WordCount ( First_Name ) - 1 ) ; First_Name )

And I get an error message "An operator is expected here. The new calc is completely highlighted.

I tried entering a ";" after the cf calc as well as putting '()" around your calc. Didn't help. What am I missing.

Thanks,

Al

[mr_vodka]

Hi Al,

Your custom function would be something like:

universalCapitalizer ( Field )

Case ( Right ( Field; 1 ) = "." ; LeftWords ( Field ; WordCount ( Field ) - 1 ) ; Field )

Then when calling the function, it would just be

universalCapitalizer ( First_Name )

[aldipalo]

Hi John:

The UniversalCapitalizer CF has been working in all my name fields, as written, for almost a year now, so I don't believe that is the problem. The error message is on the addition of Comments calc.

I tried reversing the order as well and still get the error message. I am sure the calc is correct. The problem is in combining the two. I don't see any difference in what you laid out and what I posted. But, alas, I have been known to miss certain subtleties (Like a kick in the arse) before.

So, if I am missing it please point it out directly. I have about 200 names to import and I don't want to manually correct every one of them.

Thanks,

Al

Edited March 16, 2007 by Guest

[mr_vodka]

What does this CF do?

I think I may have misread your earlier post. I had thought that what you posted was the issue in your CF (thought you were trying to incorporate comments calc into the CF); not realizing that your issue was in the calc calling it.

Let (

f = universalCapitalizer (First_Name ;"First_Name" );

Case ( Right ( f; 1 ) = "." ; LeftWords ( f; WordCount ( f) - 1 ) ; f)

)

[Lee Smith]

Mr V.

The Custom Function is posted in a Free file Here and Here

However, I'm not sure how [color:red]Al has set it up in his file. This may be accomplished easier by using a second field and extracting the [color:blue]LeftWord (CurrentField; 1) out of it.

Lee

[mr_vodka]

Thanks Lee,

I kept looking for it at Brian Dunning's site.

-)

[aldipalo]

Hi John:

Just got back to the office. It worked perfectly. Thanks,

Al