Reply
 
Thread Tools Display Modes
  #1   Report Post  
BobJustBob BobJustBob is offline
Junior Member
 
Location: Oklahoma USA
Posts: 0
Question 2007 Macro for repairing comma separated issue

I upgraded from Word 2003/XP to Word 2007/Win7-64bit recently and lost the macros associated with the old installation. I have been able to recreate all but one. Need help with this one, as it is too complicated for me to create.
The files I need the macro for consist of several hundred lines of comma delimited items, each item has double quotes around them, and the end of each line has a CR.
The problem: I need to open this data in Word and make a correction to the "," separation issue that is caused by the database that supplies the data.
Notes: The first item on each line is fixed in length (8 numbers enclosed in quotes) followed by the comma separator. The 2nd item is the one with the issue - a NAME field. If the name field contains 22 characters or less - no problem; AFTER the name and before the DATE field that follows, the normal "," pattern is there (there are examples of these below) followed by the date enclosed in quotes. If the name field contains 23 characters, the "," drops one character and becomes just NAME",DATE - missing the second quote. If the name field contains 24 characters, the "," drops another character and appears as just NAME"DATE. If the field contains 25 characters (the maximum in any of my data), the "," drops yet another character and appears as NAMEDATE with nothing separating them.
Samples (fake data):

"01388357","EVARADO STEFFHANIE RAE","10/26/12","3341"," . . . {Good Example, 22 characters in name}
"01389222","TENCCHEZ ROBENICE CLARI",10/24/12","1046"," . . . {23 chars, dropped the " before the date}
"01388214","ABC TRUCKING COMPANY INC"10/26/12","0120"," . . . {24 chars, dropped the ," before the date}
"01388220","CARONS CARTHAN SERVICE LL10/26/12","3341"," . . . {25 chars, dropped the entire "," separator}

I didn't write the lost macro from Word 2003, but am trying to recreate the solution in Word 2007 now. I would imagine a macro that moved past the first 12 characters and looked for a "," would be a good approach. If the "," was there after the 12th position of each line, before it reached a number 0-9, then it could move to the next line. If there was a " or , after the 12th position and it didn't follow the pattern "," then the macro could insert the missing separator component(s), i.e. change a ", into "," | change " to "," | insert a "," between the NAME and DATE when the 40th character is a / - (the / in the date) as this would be the only time the entire "," is missing.

Of course there are probably other methods for cleaning up my data, but this one strikes me as an efficient one, as it can skip many lines of correct data. My data usually has 300 or so lines of data and maybe 10-15 are afflicted with the incorrect format of the "," pattern.

Any and all assistance with this would be greatly appreciated! I don't have much experience with macros in Word, but have been able to find examples for my other macros that were similar enough to what I needed to adjust and recreate those.

Thanks in advance,
Bob
Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to swap words in a comma-separated list Malcolm Patterson Microsoft Word Help 1 May 27th 09 10:07 PM
Repairing Word in Office 2007 MUI fails John Butler[_2_] Microsoft Word Help 0 February 26th 08 04:25 PM
Mergefield with comma, suppress comma if field blank JB reluctantly from WP Mailmerge 3 November 17th 06 01:36 AM
Word 2002 SP3 not printing range or comma separated page values Ben A C Smith Microsoft Word Help 6 November 29th 05 04:57 PM
How do I sort a single comma separated list? ge575 Microsoft Word Help 1 February 13th 05 10:54 PM


All times are GMT +1. The time now is 01:49 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"