Google Sheets RegexReplace and Empty Cells

Question

With the help of this website and a couple of others I've nearly tackled this work related project that I originally thought far beyond my capabilities. This community has been amazingly helpful and want to thank everyone for that. Last thing I'm stuck on is currently every time I add/update data on my sheet it takes an excessive amount of time to re-calculate all the formulas being used. I believe the main reason why this is happening is due to lots of blank cells between data in one of my columns.

Here's the deal. I have a google sheet with 2 pages. Page one called Raw has 3 columns for importing "raw" data copy/pasted from elsewhere. Page two called Clean is 3 columns collecting the cleaned up data that I need. Column A and C on the clean sheet are fine and don't have many/any blank cells. Column B due to the source of the raw data and what I'm extracting out of it I'm left with significant amount of blank cells between the strings of data I actually need. Here is the formula that I'm using in column B of my clean data page: =IFERROR(REGEXREPLACE(Raw!B2,"[$][0-9]{1,4} · ","")) which as you can probably see pulls specific strings from the B column of the raw page. All the other info that doesn't match the regex leaves me with blank cells in my clean sheet.

Can anyone recommend the best way to eliminate the empty cells on the clean sheet while still pulling the info I need? Slow re-calculation is the last hurdle I need to solve before I can deploy this to use at work and make my day to day life so much easier.

Thanks in advance for any suggestions and sorry for the wall of text!

Answer 1

Google spreadsheet solution since REGEXREPLACE is not available in Excel. Add an IFERROR wrapper. Copy down so row( $A1) will increment.

=ARRAYFORMULA( INDEX( REGEXREPLACE( $B$2:$B$1000, "[$][0-9]{1,4} · ", ""), SMALL( IF( REGEXREPLACE( $B$2:$B$1000, "[$][0-9]{1,4} · ", "") <> "", ROW($B$2:$B$1000) - ROW($B$2) + 1, ""), ROW($A1))))

Answer 2

I would think it easier to make a modification to your original approach. IFERROR isn't really appropriate as it doesn't return an error if there is no match.

Change:

=IFERROR(REGEXREPLACE(Raw!B2,"[$][0-9]{1,4} · ","")) 

To:

=REGEXREPLACE(Raw!B2,"[$][0-9]{1,4} · (.*)","$1")

Basically, create a capturing group for the "keep" stuff by surrounding it in parenthesis.

(.*)

Then drop the match stuff at the beginning. Then you reference the capturing group with:

$1

This will result similar to as follows:

Cell B1 Formula: =REGEXREPLACE(A1,"[$][0-9]{1,4} · (.*)","$1")

  |         A         |       B        |
  |____________________________________|
1 | $55 · Barley      | Barley         |
2 | $2 · Squid        | Squid          |
3 | Bread             | Bread          |
4 | $27 · Gun Powder  | Gun Powder     |
5 | 3" of Filament    | 3" of Filament |

Anything that doesn't match [$][0-9]{1,4} · will pass through your RegEx filter.