Copying fields with commas

[tarek]

Hi all,

 

I'm hoping someone can help me with a macro I'm trying to make. Basically what I'm trying to do is copy a bunch of text fields which contain names. The fields are in the format LastName, FirstName. What I want to do is copy the LastName into a variable and copy the FirstName into a different variable so then I can paste them into separate fields where I need them. So a quick example is a field: Doe, John. I would want to copy Doe into a variable and John into a different variable. Basically I need to copy the stuff before the comma, and then the stuff after the comma (not including the space).

 

Another problem with that is that sometimes the fields will be: Doe, John & Doe, Jane. In this case I would still just want to copy John for firstname and Doe for lastname and ignore the rest. Any ideas?

 

Thanks for any help with this!!

 

-Gogi

[Cory]

<TVAR2:01:01:Parker, Fred><IVAR2:01:13:1:,><TMVAR2:10:02:01:001:N01:><TMVAR2:21:02:00:000:000:,><TMVAR2:11:01:00:001:N01:><TMVAR2:01:01:00:000:000:><TBOX4:T:1:CenterCenter000278000200:000:ResultsFirst name: "%T1%"
Last name: "%T2%">

You can do a similar trick using the Ampersand and spaces for your second example.

[rberq]

Very clever, Cory. Much better than looping through one character at a time as I was going to suggest.

 

Here is a slight elaboration on Cory's method, which will strip off the possible second name. It relies on there being

at least one space immediately before the ampersand. If your names are in a text file, you can wrap the whole thing in

"Text File Begin Process" and "Text File End Process" to bring the lines one at a time into the T1 variable.

 

<TVAR2:01:01:Parker, Fred & Parker, Joan><TMVAR2:07:01:00:000:000: ><IVAR2:01:13:1:,><TMVAR2:10:02:01:001:N01:><TMVAR2:21:02:00:000:000:,><TMVAR2:11:01:00:001:N01:><TMVAR2:02:01:00:000:000:><IVAR2:01:13:1: ><TMVAR2:10:03:01:001:N01:><TMVAR2:21:02:00:000:000: ><TBOX4:T:1:CenterCenter000278000200:000:ResultsFirst name: "%T3%"

Last name: "%T2%">

[stevecasper]

Here is a slightly more elaborate way to change your "Lastname, Firstname" to a "Firstname Lastname", also removing

any unwanted second parties. If there is a second person, it MUST be separated by an ampersand for this particular

macro to eliminate it, though if a different separator were to be used, it is a simple modification of the "Set Integer from

Position of Text" command.

 

Why this one is more elaborate is because it does more than just get rid of the comma and switch the names around, it

also creates a .csv file that can be opened in your spreadsheet application, and even saved as an .xls... assuming you

were ever to want to collect these names in that way. This macro only goes far enough to create the .csv file, but it

shouldn't be hard to add one more "Copy File or Files" command to create the .xls.

 

The one drawback about this particular method, or at least as far as I built this macro, is that T1 remains the Lastname,

and T2 becomes the Firstname, but that shouldn't be a problem to switch around at the end. This also puts the last name

in the first column of the .csv file and the first name in the second column. To fix that, there would need to be code to

switch them before they are put into the .csv. Probably the code that Cory gave originally.

 

Anyway, here it is, for anybody who is interested:

 

Variable Set String %T1% "Parker, Fred & Parker, Joan"
Variable Set Integer %N1% from Length of Variable %T1%
Variable Modify Integer: %N1% = %N1% + 1
Variable Set Integer %N2% from Position of Text in Variable %T1%
Variable Modify Integer: %N1% = %N1% - %N2%
Variable Modify String: Delete Part of %T1%
Replace " " with "" in %T1%
If File Exists "testforgogi.csv"
 Variable Modify String: Append %T1% to Text File
Else
 Variable Modify String: Save %T1% to Text File
 Copy File or Files: "testforgogi.txt"
End If
Variable Set Integer %N1% to 1
ASCII File Begin Process: "testforgogi.csv" (Comma Delimited Text )
 Variable Modify Integer: Inc (%N1%)
ASCII File End Process
ASCII File Begin Process: "testforgogi.csv" (Comma Delimited Text )
ASCII File End Process
Delete File or Files: "testforgogi.txt"
Text Box Display: Results

 

<TVAR2:01:01:Parker, Fred & Parker, Joan><IVAR2:01:12:1><NMVAR:01:01:1:0000001:2:0000001><IVAR2:02:13:1:&><NMVAR:02:01:1:0000001:1:0000002><TMVAR2:11:01:00:N02:N01:><TMVAR2:21:01:01:000:000: ><IFOTH:01:2:C:\Users\Steven\Documents\Macros\Macro Test Files\testforgogi.csv><TMVAR2:20:01:00:000:000:C:\Users\Steven\Documents\Macros\Macro Test Files\testforgogi.csvT><ELSE><TMVAR2:17:01:00:000:000:C:\Users\Steven\Documents\Macros\Macro Test Files\testforgogi.txtT><DOFILE:05:NN:C:\Users\Steven\Documents\Macros\Macro Test Files\testforgogi.txt>C:\Users\Steven\Documents\Macros\Macro Test Files\testforgogi.csv><ENDIF><IVAR2:01:01:1><ADFBEG:F10:001:000001:000000:C:\Users\Steven\Documents\Macros\Macro Test Files\testforgogi.csv><NMVAR:08:01:0:0000001:0:0000000><ADFEND><ADFBEG:F10:001:N00001:000001:C:\Users\Steven\Documents\Macros\Macro Test Files\testforgogi.csv><ADFEND><DOFILE:08:NN:C:\Users\Steven\Documents\Macros\Macro Test Files\testforgogi.txt>><TBOX4:T:1:CenterCenter000278000200:000:ResultsFirst name: "%T2%"
Last name: "%T1%">