I'm stuck on something. I've tried to run the following GREL transformation across all columns in my table (i.e. I choose the "Transform" option under the "All" column):
value.replace("Chatman, Robby F.", "Chatman, Robert F.")
When I run it, the process only converts some the cell values that are Chatman, Robby F. When I use a conventional replace on a single column (i.e Edit cells>Replace), all values are successfully replaced.
I've found that sometimes I run into issues with find and replace because the spaces in between the strings differ from one string to the other. Namely, sometimes the space is a nonbreaking space, and sometimes they're conventional "space bar" spaces. So If I replace the first with the second, replacing works. However, in this case, I've double checked and all of the strings Chatman, Robby F. have conventional spaces, so the transformation should work.
I've never used the transform function across all columns so maybe I am missing something? My table is about 56,000 rows of data and 20-plus columns--perhaps the transform process times out at a certain limit?
You and add the following GREL value.replace(/[\p{Zs}\s]+/,' ') to collapse all consecutive whitespace before applying your transformation.
You can chain your two replaces in a single expression this way:
value.replace(/[\p{Zs}\s]+/,' ').replace("Chatman, Robby F.", "Chatman, Robert F.")
Using the All > Transform works the same as running the expression on each column individually. Under the Undo/Redo tab, you can confirm how many replace on each column were performed, and confirm if it missed a column or not.
Thanks, Martin. I removed any extra whitespace before I ran the transformation. And running it on an individual column, it changes some of the values but not all (same as the global transformation). Furthermore, find and replace works fine on these values in Excel! I'm assuming it's some hidden character/encoding issue that I can't figure out and which Excel forgives but OpenRefine doesn't. So I just wrote a Powershell script to find and replace on multiple values. Anyway, appreciate your response. It remains a mystery but at least I've figured out a workaround.
Facets are a good tool to help debug things like this. If you set up a facet with your regex, you can then examine the non-matching values to see what they have in common.
on this issue