I have a large Excel table containing data for a law firm directory (first name/middle initial or maiden name/last name/date started/date ended/school attended/year graduated/business title/business address [3 cells]/bus. phone/bus. email/home address [3 cells]/home phone/home email). My wish is to create a GREP script that would translate the many columns of data into formatted text. However, with my limited understanding of GREP, the best I could come up with was a set of find/replace presets (e.g. replace line breaks with spaces, replace line break after last name with three spaces, replace line break between two dates with space-hyphen-space, replace line break after graduation date with hard return and "BUSINESS", etc.) which I used over and over again on the imported unformatted Excel data. It did save time, but not enough time, and my hand felt like it would fall off after formatting over 100 pages. This is what I want the text to look like:
John A. Doe 1980-1985
Doe, Doe and Doe
123 Any Street
Anytown, MA 02138
123 Any Lane
Anytown, MA 02138
The tricky thing is (I think) that any number of cells in a particular row could be unfilled (some folks don't fill in the middle name cell, some only fill in the "business info", some only the "home" info), so the script would have to be able to skip blank cells.
One of the main problems I encountered when trying to string several of my GREP searches together, was that when I tried to use "$1,$2,etc." as placeholders, the text I was trying to keep was replaced with a literal "$1" or "$2". I still can't figure out why that happens.
Anybody able to help on this? It's probably too late for this project, but I'd like to know how to do it for the next one.
Thanks in advance,
You'd be better off using Data Merge - then import all the info directly from the Excel sheet.
This should give you a good overview of how it's done
Thanks, Euigene! For some reason I had thought that Data Merge wasn't the right thing for this, but I used it on two more Excel tables, with very good results, and it saved a lot of time.
Now I just need to figure out how to do an index...
thanks very much for your help.