CODEWITHSUNDEEP
powershellpowershell-5.0
Cameron
Cameron

Reputation: 171

Extract multiple Substrings from String

I have a xlsx file which contains a listing of barcodes, which are listed three or four to a cell, which I need to split up so I only have the barcode.

The barcodes themselves are always strings of 6 numbers, but they may be prefaced with several different letters and there may or may not be commas, ampersands, and other words in the cell with them. It looks something like this:

COL 1 | COL 2 | COL 3 | COL 4 | COL 5
Info  | Identifier | Info  | Info  | L123456 , PC 654321 , M 123654 & 546123 Vacant |
Info | Identifier | Info | Info | PC 123456 , M 456789 Occupied
Info | Identifier | Info | Info | L 987654

So far I have attempted to use regex to remove all the noise data and just be left with the barcodes, but this has been returning a jumbled mess.

I also need to have a way to keep track of which row they came from, as there is an identifier in an earlier column that needs to be linked to these barcodes. I am able to access this identifier quite easily.

I am using an excel ComObject for manipulating this sheet. This is the code I was using to attempt the regex, how can I extract the barcodes?

$xl = new-object -ComObject excel.application
$xl.visible = $true
$xl.displayalerts = $false
$xl.workbooks.open("file.xls")
$sheet = $xl.activeworkbook.activesheet
$x = 3
3..8|%{
    $uc = $sheet.Range("B"+$x).Text
    $equip = $sheet.Range("I"+$x).Text
    $loc = $sheet.Range("D"+$x).Text + '-NHQ'
    $uidcc = $uc.replace(" / ",",")
    $tagnums = $equip -replace " [A-Z]+ ",""
    $tagnums = $tagnums -replace " & ",""
    $tagnums = $tagnums -replace "[A-C][1-9]+",""
    $tagnums = $tagnums -split ','
    foreach($i in $tagnums){
        $asset += $i+","+$loc+","+$uidcc+"`n"
    }
    $x++
}
$asset | Format-Table
$xl.quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($xl)

Upvotes: 1

Views: 823

Answers (1)

Andrei Odegov
Andrei Odegov

Reputation: 3429

If i understand you right then you are need something like this:

$tagnums = @([regex]::matches($equip,'\D*(\d{6})')|%{$_.groups[1].value})

For example, for the input data 'L123456 , PC 654321 , M 123654 & 546123 Vacant' will be the next output:

123456
654321
123654
546123

and for 'L 987654' will be 987654.

Upvotes: 1

Related Questions