5 Replies Latest reply on Sep 14, 2017 5:37 AM by vinny38

    GREP : Replace empty spaces


      Hi !


      I've been working on this all day and have been able to find only half an answer so I come here full of hope… ! =)

      I have a table which I import from Excel. In this table I have empty cells sometimes and I want to put a "-" in them.


      I tried this grep request to find the empty spaces : (?<!.)$

      Which seems to work, it finds empty spaces in the format I searching when I press "Next"


      I tried to "replace" the empty spaces using : $0-

      Which, oddly enough, works when I press "Replace" but doesn't when I press "Replace all"


      Here is my problem then, how can I make it work all at once ? Since I don't want to replace them all at once… ^^''


      Thanks in advance !

        • 1. Re: GREP : Replace empty spaces
          winterm Level 5

          It ought to be as easy as Find: $ and Change to: - ... but it's not.

          You may find interesting this thread. Don't bother with the answer marked as 'correct', look for Jongware's posts 6 and maybe 14.

          Didn't try it myself, though.

          1 person found this helpful
          • 2. Re: GREP : Replace empty spaces
            vinny38 Level 5


            This is an interesting puzzler.

            Like winterm said before, you probably need a script to achieve this.
            I haven't tested Jongware's script either, but I'm sure it would do the job...


            Although, I would like to share a few thoughts :


            • In Excel, you can easily write and run a macro to replace empty-cell by a dash. Something like this:
            For Each cel In Range("A1:Z200") //Don't forget to adjust your Range
              If IsEmpty(cel.Value) Then cel.Value = "-"

                    So, if you use linked file, it could be an acceptable solution.


            • If you use a very simple table, with no merging, no funny stuff and that you just use a table style, you can also convert table to text, run a Grep query to look for 2 consecutive column separators, separator at the beginning and at the end of a paragraph. Finally revert text to table and apply your table style.
              This is quite a brutal workaround, and you should be very cautious with it... (I wonder if I should have shared this crazy thought ^^)


            • I was very curious and surprised you said that your Grep query did allow you to "Replace" (even only once)...
              Since nothing is selected, how could "Replace" work?
              So I tested your GREP query, and... it didn't work for me... (Windows 7 - Indd CS6)
              But... copying a dash into the pasteboard and replacing by ~C did work somehow, and still surprisingly... with the same limitation than you though: "Replace All" does not work (Obviously !).
              However Find/Replace button did the job and if you don't have thousands of empty cells, it can be an acceptable workaround.

            1 person found this helpful
            • 3. Re: GREP : Replace empty spaces
              winterm Level 5

              Point 1. Macros in Excel still aren't my friends, couldn't comment.

              Point 2. I must confess I also considered (silently) this trick, but found it too drastic and didn't dare to share... Hey man, you're the brutal beast!

              Point 3. Ha! Again, we think alike! Also played this all (incl. clipboard contents), and got identical results. So could only confirm here.

              Jongware's script is the way to go, imo.

              1 person found this helpful
              • 4. Re: GREP : Replace empty spaces
                Obi-wan Kenobi Level 5

                Theun is right here!


                Place the cursor in any cell! Short version:


                var a = app.selection[0].parent.parent, b = a.cells,  c = b.length;
                while (c--) if (b[c].contents.length == 0) b[c].contents = "-";




                2 people found this helpful
                • 5. Re: GREP : Replace empty spaces
                  vinny38 Level 5
                  1. They can be so handy combined with INDD
                  2. Glad I'm not the only one with a twisted mind
                  3. see point 2


                  Jongware's script is the way to go, imo.

                  Well, that's definitely true!