Convert Names to Variable

[Amerifax-Bob]

MC Donald MIKE & MARY

 

The "&" tells me a lot. The wife follows the &. Mike and Mary are easy to identifiy, most of the time. If the last name has a Mc Donald II Mike & Mary, thats not good either. The last name can be a problem. Has anyone worked with this problem. I need to end up with the following.

 

Last Name = T1

First Name = T2

Secong Name = T3

 

Bob

[Cory]

I have a new client that I do a lot of data parsings for like this. There's all kinds of garbage and oddities like this. I have written several macros specifically for them to deal with stuff like this and have several dozen of my own as I tend to do a lot of data manipulations like this. What I have found is that making several individual tools works better than trying to write a panacea macro. The user might have to run half a dozen macros but it grants a huge amount of versatility. So I guess "yes" is the answer.

 

In this case we opted to make a macro that would take list A and create a similar list B that would be identical to list A except it would make two lines out of any that had an ampersand. In your example we would result in "MC Donald, Mike" and "Mc Donald, Mary" on two separate lines. But you could have a spouse column as well if you like. In either case it's very easy to do.

 

Like in the last reply I sent you it needs to be in a variable. I'll assume you're using something like ASCII File Repeat to get the vars and you want a last name, first and spouse. First you identify if it needs to be split up. To do this use a conditional statement "If" with an "Else". So If T1 contains an ampersand do one thing, and if not do something else. Now since you don't have a comma we have a problem and are forced to make an assumption. What if we have "Mc Donald Mike" and "Smith Billy Bob" in the same data set? Well there's no foolproof way of determining, is there? Since multi-string names are more common in last names by about 100 times I will assume the first name is a single string. But to be sure I'll later test all the last names for the existence of a space and flag those records so I can eyeball them to be sure. You can also make whitelists of common multi-string last names like "De La Cruz" but that's another topic.

 

So if there was an ampersand I would identify it's location and parse what follows to the spouse variable trimming out spaces and such. Next I would step backward thru the remaining string to find a space. Now I would break out that last chunk to the first name. What's left over is the last name. Something like this:

<TVAR2:01:02:FGive me a test stringFFCenter:Center><REM2:Do this section if there is an ampersand ><IFVAR2:1:01:7:&><REM2:Find the ampersand location ><IVAR2:01:13:1:&><REM2:Move teh position over to the left one to the space><NMVAR:09:01:0:0000001:0:0000000><REM2:Copy the raw chunk to the spouse name field><TMVAR2:10:03:01:N01:999:><REM2:Delete the ampersand and spouse name><TMVAR2:11:01:00:N01:999:><REM2:Delete the ampersand and spaces from spouse field><TMVAR2:11:03:00:001:003:><ENDIF><REM2:This section gets done in either case><IVAR2:01:12:1><REM2:Goes thru each character looking for last space><REP3:01:000001:000001:%N1%:1:02:><REM2:Copies one character to T4 for evaluation><TMVAR2:10:04:01:N02:001:><REM2:If it's space then record it. If there are more than one spaces the last one will be the value recorded.><IFVAR2:1:04:1: ><REM2:records the space position for later use><NMVAR:07:02:0:0000003:0:0000000><ENDIF><ENDREP><REM2:Copy part of the original string to the first name field T2><TMVAR2:10:02:01:N03:999:><REM2:Deletes that same section from T1. T1 is now complete><TMVAR2:11:01:00:N03:999:><REM2:Remove the space from the beginning of the first name field><TMVAR2:11:02:00:001:001:><TBOX4:T:1:CenterCenter000278000200:000:ResultLast name: "%T1%"
First name: "%T2%"
Spouse name: "%T3%">

Of course it can be tweaked to your liking.

 

BTW I'm availible for hire also if you ever need more help with these things.