Author Posts

October 25, 2017 at 12:52 pm

I have an excel spreadsheet xlsx that I need to manipulate, one of the columns called Supersedes has data like:-

MS16-034[3139852]

But it could have multiple separated by a comma i.e.

MS16-034[3139852]

All I am interested in is the data between the square brackets ( I can use the following regex to get this bit [regex] "\[([^\[]*)\]" ) if it a singe instance.

What I need to be able to do is import the data then manipulate the data in the object that I import. Lets say the name of the field is Supersedes if the data in Supersedes is just a single like MS16-034[3139852] just strip everything outside the square brackets just leaving 3139852 in this case, but if the contents has multiple pieces of data i.e. MS13-082[2863240],MS15-048[3032655] split the data between the brackets into fields i.e. supersedes and supersedes1 etc

Any ideas

October 25, 2017 at 1:50 pm

If the values are always comma-delimited, I'd -split the string on a comma first, and then operate the regex against each. You could also write a regex that will capture multiple matches from a single string.

October 25, 2017 at 3:55 pm

Thanks Don.

I will have another play with it, more Coffee needed I think 🙂