see if these help:
I think I would try Char(160).
And there are other ways to parse out a name like this. There may be Custom Functions to make sure the last name is extracted correctly.
Thank you Beverly. A calculation including Char (160) almost worked.
This calc below works in DataViewer but not when used in Find/Replace script step.
If I paste the results in DataViewer to Full_Name, the calculation field correctly joins the 2 right words with a non-breaking space.
If I paste the calc into the Find/Replace script step, it doesn’t add the non-breaking space.
Any ideas or suggestions?
I modify the calculated field:
LeftWords ( FirstName ; 1) &
Case( WordCount (FirstName )>1;
" " & MiddleWords (FirstName;2 ; WordCount (FirstName )-1);
& ", " & LastName
I hope you helpful
I'm afraid I can't quite see the logic of your calc so am not surprised it's tripping you up, but it seems to me that you may be attacking the issue at the wrong point. If it were me I think I would be going back to the First Middle and Last name fields and using Substitute( ) to replace any standard spaces with non-breaking spaces. After that the Full Name calc should work.
In the example you give, my hunch is that Van has ben imported into the Middle name field instead of as part of the Last name. Depending on the extent of this kind of issue you could fix this and any other similar anomalies (e.g. you might have imported Lisa-Maree into First and Middle name fields where they belong together as a single First name) manually, or if there are many instances you could Find the relevant records (e.g. all records with Van as a Middle name) and then use Replace Field Contents in the Last name field to concatenate the two into the Last name field complete with non-breaking space, then RFC on the Middle name field to blank the Van from there.
In my last post, I was asking why a calc field that works in Data Viewer is not working in a Find / Replace script step.
To clarify - there are no First, Middle or Last name fields in the file that I am importing. Just one name field, FullName that contains each person’s full name with first name, middle initial (some) and last name all in one field. There are no other name fields such as First, Middle or Last Name.
I want to alphabetize the list by last name. (e.g. Smith, John). I use a calc field c_LastNameSort for this. It works fine except in cases like my example where the last name is the last two words. Inserting the non-breaking space between the last two words solved the problem.
The calc I posted is part Find/Replace step in a script that finds specific FullNames such as John Van Heusen that need the non-breaking space between Van and Heusen.
If I paste the results of this calc from Data Viewer into the FullName field, the calc field works fine. The same calc in a Find / Replace script step does not work.
Substitute is a good suggestion though. I’ll try it.
"Find / Replace" don't allow any calculation, it is treated as literal text.
("Find Records" then) "Replace Field Contents" will do.
OK I misunderstood your description. Nevertheless, the setup I described would give you much simpler control and I suggest you consider it. You might use you current full name field simply for import purposes—to contain the name exactly as imported from the spreadsheet. Then you could create the separate name fields and populate them by calculation—using Replace Filed Contents, if you wish. Thereafter you can proceed as per my last post. I usually create two calculated name fields to concatenate name data in various ways (e.g. First name & " " & Last name; or Last name & ", " & First name & Middle initial; etc).
"any calc" isn't correct in script, I should say "calculation is evaluated once before replace, not on each record".
And, if you replace a field, you need to go to the field before replace.
"Find / Replace" works as word processor, it is used only limited situation.
How do you distinguish between someone that has First Middle Last and someone that is First TwoWord Last ( no middle name )?
This may be question for sccardais, I think he has list of 2 words last name.
Then for each the last name, replace last space to Char(160)
Enter Find Mode
Set Field [FullName; "*" & "LastName thatHas2Words"]
Replace Field Contents[FullName; Replace ( FullName ; Position ( FullName ; " " ; 1 ; PatternCount ( FullName ; " " ) ) ; 1 ; Char(160) )]
I prefer having separate field that keywords wrote.
this is a better custom function that should help:
"c. 2012 Drewsol. This CF parses specified name parts (first, last, middle) from a single name field for the flexibility of sorting and/or display. fnNameStraight now handles Spanish names, and some middle-eastern names."
it looks like there is a section for adding these multiple-word-lastname, so may need to be edited.
First - thank you to everyone who has replied.
User19752 - I think you nailed it. I didn't know that Find/Replace couldn't accept calcs. Did I understand you correcctly? This is surprising since the calc engine is available with this script step but it would explain why Find / Replace didn't work with my original calc or when I tried Substitute. I'm going to try Replace Field Contents.
Joshua Ormond: I search for specific names that I know have the issue. This is a payroll system with relatively few records in each batch and even fewer with the problem that started this thread. Each batch has the same small number of records with this issue. In my script "After Import", I search for specific people and apply corrections to each individually.
CarlosSilvia0: The values in FullName don't have the same number of "words." Some have 2 - only First and Last. Some have 3 with a middle initial or name. Some have 4. Because of this, I don't think the modified calc field you suggested will work in my situation.
Beverly Voth: Thanks very much for providing the various links and resources. I'll look at CF #1020. It would be great if the last two words in my "problem names" were separated by a hyphen (as mentioned in CF# 1020)!
All - just to be clear - I am importing from a spreadsheet that contains a single field for FullName. The number of words in this field can be 2, 3 or 4. Some of the FullNames have the problem I mentioned. I have a script, "After Import" that performs various maintenance functions on each batch of imported records. One part of this script looks for specific people that use two words in their last name. I don't scan the entire batch of imported records. I know who to look for and I find their records specifically. My script finds these people one by one. Once found, I want to add the non-breaking space between the last two words in the FullName field so the calc in another field, c_LastNameSort works for all records. Normally, I'd be breaking FullName into separate fields but that is not necessary in this case.
Thanks to all for taking the time to reply.
The proposed function manages any number of words.
Just to close this out ...
User19752 suggested using "Replace Field Contents" rather than "Find/Replace" and it worked.
In my "After Import" script, I first find the people with the "problem names" than I Replace Field Contents with a calculation that inserts Char(160) - non-breaking space - between the last two words in the FullName field. The makes the calc in c_LastNameSort work perfectly. e.g. "Heusen, John Van" becomes "Van Heusen, John".
Thanks again to all.