Excel Help

This topic contains 2 replies, has 2 voices, and was last updated by  Simon B 3 weeks, 5 days ago.

  • Author
    Posts
  • #82843

    Simon B
    Participant

    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

  • #82853

    Don Jones
    Keymaster

    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.

  • #82883

    Simon B
    Participant

    Thanks Don.

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

You must be logged in to reply to this topic.