Replace null with string

[Amy Rudersdorf]

Hello. 

There is probably an obvious answer to this question, but I can't find anything in previous posts, expressions, functions, and recipes I've looked through.

I have a column (Column B) that contains many different values in addition to blank cells. I want to search within Column B on a blank cell and replace it with a value [value 3], based on a value in Column A. (E.g., Column A contains values like "University of Southern Place" and "Northern University." When Column A contains "University," I want to populate Column B with the term "Education.")

All cells are defined as "text." Here's what hasn't worked:

1. Edit Cells > Fill down

Column B looks like this:

[blank]

[value 1]

[value 1]

[value 1]

[blank]

[blank]

[blank]

[blank]

[blank]

[value 2]

[value 1]

[value 2]

[blank]

If I edit the first cell to add a [value 3] and then choose Edit Cells > Fill down, the first set of blanks are replace by [value 1] and the second by [value 2], not [value 3] as I'd hoped (expected?).

2. First facet OR filter Column B on "blank" (Facet > Customized facet > Facet by blank OR Text Filter) and then try to Edit Cells > Fill down.

So, now Column B looks like this:

[blank] 

[blank]

[blank]

[blank]

[blank]

[blank]

[blank]

[blank]

In the first go-round I faceted on blank and chose "true," Then, I edited the first cell to add [value 3] and hit Edit Cells > Fill down. The result was that the cell I edited disappeared and I was still left with a column of blanks. This makes sense...although it is disappointing. I also tried filtering by "blank" and then followed the same subsequent steps. I got the same results.

I've looked at the recipes (https://code.google.com/p/google-refine/wiki/Recipes), Understanding Expressions (https://github.com/OpenRefine/OpenRefine/wiki/Understanding-Expressions), and GREL String Functions (https://github.com/OpenRefine/OpenRefine/wiki/GREL-String-Functions), but none of these *appear* to answer my need. Since I'm working with a data set that contains 25K records, I'm pretty desperate to find an answer.

Admittedly, I'm a newbie...but this is a pretty easy thing to do in Excel, so I can't figure out why I'm having so much trouble with it here. 

Thanks for any assistance y'all can provide.

Amy

[Tom Morris]

Try this:

- facet on blank for Column B and select True

- text filter on column A with the string "University"

- Edit cells->Transform on Column B with "Education"

Tom 

[Jose Manuel Ceppi]

if(and(isBlank(value),cell['Col A'].value.contains('University')), 'Education', value)

Go on Create column based on this column, then copy and paste what I wrote above, it should do what you are asking for.

Bests of luck,

Jose