3 Replies Latest reply on Sep 27, 2012 12:47 PM by Carla Cantone

    Need help using GREP find/replace on Excel data

    Carla Cantone

      Hi All,

       

      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
      Yale 1979

      BUSINESS

      Of Counsel
      Doe, Doe and Doe

      123 Any Street
      Anytown, MA 02138

      (781) 555-5555

      johndoe@doe.com

      HOME

      123 Any Lane

      Anytown, MA 02138

      (781) 555-1234

      johndoe@yahoo.com

       

      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,

       

      Carla