Extract multiple Substrings from String

Question

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)

Andrei Odegov · Accepted Answer · 2017-04-14 19:44:51Z

1

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:

and for 'L 987654' will be 987654.

edited Apr 14, 2017 at 19:44

answered Apr 14, 2017 at 19:19

Andrei Odegov

3,5722 gold badges18 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrei Odegov Over a year ago

It's possible to simplify the regex till '\d{6}'

Collectives™ on Stack Overflow

Extract multiple Substrings from String

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related