Excel Help

Welcome Forums General PowerShell Q&A Excel Help

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
1 year ago.

  • Author
    Posts
  • #82843

    Participant
    Points: 13
    Rank: Member

    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

    Keymaster
    Points: 1,638
    Helping HandTeam Member
    Rank: Community Hero

    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

    Participant
    Points: 13
    Rank: Member

    Thanks Don.

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

The topic ‘Excel Help’ is closed to new replies.