How can I add a string character based on a position in OpenRefine?

Question

I have a column in Openrefine, which I would like to add a character string in each of its rows, based on the position in the string. For example: I have an 8th character number string: 85285296 and would like to add "-" at the fourth place: "8528-5296". Anyone can help me find the specific function in OpenRefine?

Thanks

Tzipy

score 3 · Answer 1 · answered Aug 13 '19 at 15:28

3

The simplest approach is to just use the expression language's built-in string indexing and concatenation:

value[0,4]+'-'+value[4,8]

or more generally, if you don't know that your value is exactly 8 characters long:

value[0,4]+'-'+value[4,999]

answered Aug 13 '19 at 15:28

Tom Morris

10,490
32
53

score 2 · Accepted Answer · answered Aug 13 '19 at 13:13

2

Possible solution (not sure if it's the most straightforward):

value.replace(/(\d{4})(.+)/, "$1-$2")

This means : if $1 represents the content of the first parenthesis/group in the regular expression before and $2 the content of the second one, replaces each value in the column with $1-$2.

answered Aug 13 '19 at 13:13

Ettore Rizza

2,800
2
11
23

1

Very clever, but not very approachable for a novice, in my opinion. Regexs are a pretty advanced topic. – Tom Morris Aug 13 '19 at 15:31

score 1 · Answer 3 · answered Aug 13 '19 at 15:11

Some other options:

value.splitByLengths(4,4).join("-")

value.match(/(\d{4})(\d{4})/).join("-")

value.substring(0,4)+"-"+value.substring(4,8)

I think 'splitByLengths' is the neatest, but I might use 'match' instead because it fails with an error if your starting string isn't 8 digits - which means you don't accidentally process data that doesn't conform to your assumption of what data is in the column - but you could use a facet/filter to check this with any of the others

How can I add a string character based on a position in OpenRefine?

3 Answers3