Extract two sets of data only (barcode and first address) from a scanned pdf147 barcode into separate columns on a worksheet.

[Bassmank]

Hi to everyone on Mr Excel I have now joined a as member but I have used Mr Excel for years for help on similar issues by other users on the forum and is indispensable for helping to resolve a lot of issues I have had using Excel.

My problem is that I am trying to extract the following data from a scanned pdf147 barcode as I am not familiar with working on this type of scanner format.

I have tried using find and replace to convert the ||, ||| and ++ characters to commas then replaced theses with spaces and then using the text to columns command to create the barcode and address colours but it is now becoming very long winded clunky and also very time consuming.

I have no interest in the rest of the data after the barcode and address information only the first sections of the scanned barcodes but if all the information can all be separated out if this makes formulas and formatting easier then that’s no problem.

I am assuming that the || and +++ can be used as say string handlers to help separate all the data sections individually.

Once the data is in separate columns i could then be able to export the barcode and address information as a csv format which I can then import to my route optimiser instead of typing it all in which is now getting very time consuming as there are now usually 70 to 80 address to work with at any one time.

Please note that the customer address details and info have all been changed to preserve data protection.

Scanned Barcode cell data:
[)1.3++T00H2A0080706427++614||001++UK16725593||++NDAY||||++Mr David Smith||20 Joe Blogs Street||East Calder||Livingston||||||||EH49 5SD||GB||+468930132517||0002||++1978||1||1||1||5333||GBP||++++004||570196||||||57||85++||++Boohoo||HEASANDFORD INDUSTRIAL ESTATE||WIDOW HILL ROAD||BURNLEY||LANCASHIRE||GB||||BB10 2BQ++27112021||(]

Columns required
Barcode column
barcode format: T00H2A0080706427

Note: Extracted from [)1.3++T00H2A0080706427++614||

First address only
Cell Address Column Data

Mr David Smith
20 Joe Blogs Street
East Calder
Livingston
EH49 5SD
GB

Note: Extracted from
++Mr David Smith||20 Joe Blogs Street||East Calder||Livingston||||||||EH49 5SD||GB||

If anyone can offer any help with say VBA or a suggested formulas eg trim, mid substitute functions to extract the information it would be very greatly appreciated appreciated.

Many thanks to all Mr Excel users

Cheers kevin

 

[ranman256]

paste this code into a module then the result can load into a cell: getAddViaBC()

Public Function getAddViaBC()
Dim vWord, vName, vAddr, vAdd2, vAdd3, vAdd4, vAdd5, vAdd6, vPost, vCtry
Dim vAddrBlock
Dim i As Integer, x As Integer

vWord = Replace(ActiveCell.Value, "+", "")
For x = 1 To 8
  i = InStr(vWord, "|")
  If i = 0 Then
    Exit For
  Else
    vWord = Mid(vWord, i + 1)
  End If
Next

vName = getNextFld(vWord)
vAddr = getNextFld(vWord)
vAdd2 = getNextFld(vWord)
vAdd3 = getNextFld(vWord)
vAdd4 = getNextFld(vWord)
vAdd5 = getNextFld(vWord)
vAdd6 = getNextFld(vWord)
vPost = getNextFld(vWord)
vCtry = getNextFld(vWord)

vAddrBlock = vName & vbCrLf & vAddr & vbCrLf
If vAdd2 <> "" Then vAddrBlock = vAddrBlock & vAdd2 & vbCrLf
If vAdd3 <> "" Then vAddrBlock = vAddrBlock & vAdd3 & vbCrLf
If vAdd4 <> "" Then vAddrBlock = vAddrBlock & vAdd4 & vbCrLf
If vAdd5 <> "" Then vAddrBlock = vAddrBlock & vAdd5 & vbCrLf
If vAdd6 <> "" Then vAddrBlock = vAddrBlock & vAdd6 & vbCrLf
vAddrBlock = vAddrBlock & vPost & " " & vCtry

''MsgBox vAddrBlock
getAddViaBC = vAddrBlock
End Function


Private Function getNextFld(pvWord)
Dim i As Integer
Dim vRet

 i = InStr(pvWord, "||")
vRet = Left(pvWord, i - 1)
pvWord = Mid(pvWord, i + 2)
getNextFld = vRet
End Function

 

[Bassmank]

paste this code into a module then the result can load into a cell: getAddViaBC()

Public Function getAddViaBC()
Dim vWord, vName, vAddr, vAdd2, vAdd3, vAdd4, vAdd5, vAdd6, vPost, vCtry
Dim vAddrBlock
Dim i As Integer, x As Integer

vWord = Replace(ActiveCell.Value, "+", "")
For x = 1 To 8
  i = InStr(vWord, "|")
  If i = 0 Then
    Exit For
  Else
    vWord = Mid(vWord, i + 1)
  End If
Next

vName = getNextFld(vWord)
vAddr = getNextFld(vWord)
vAdd2 = getNextFld(vWord)
vAdd3 = getNextFld(vWord)
vAdd4 = getNextFld(vWord)
vAdd5 = getNextFld(vWord)
vAdd6 = getNextFld(vWord)
vPost = getNextFld(vWord)
vCtry = getNextFld(vWord)

vAddrBlock = vName & vbCrLf & vAddr & vbCrLf
If vAdd2 <> "" Then vAddrBlock = vAddrBlock & vAdd2 & vbCrLf
If vAdd3 <> "" Then vAddrBlock = vAddrBlock & vAdd3 & vbCrLf
If vAdd4 <> "" Then vAddrBlock = vAddrBlock & vAdd4 & vbCrLf
If vAdd5 <> "" Then vAddrBlock = vAddrBlock & vAdd5 & vbCrLf
If vAdd6 <> "" Then vAddrBlock = vAddrBlock & vAdd6 & vbCrLf
vAddrBlock = vAddrBlock & vPost & " " & vCtry

''MsgBox vAddrBlock
getAddViaBC = vAddrBlock
End Function


Private Function getNextFld(pvWord)
Dim i As Integer
Dim vRet

 i = InStr(pvWord, "||")
vRet = Left(pvWord, i - 1)
pvWord = Mid(pvWord, i + 2)
getNextFld = vRet
End Function

paste this code into a module then the result can load into a cell: getAddViaBC()

Public Function getAddViaBC()
Dim vWord, vName, vAddr, vAdd2, vAdd3, vAdd4, vAdd5, vAdd6, vPost, vCtry
Dim vAddrBlock
Dim i As Integer, x As Integer

vWord = Replace(ActiveCell.Value, "+", "")
For x = 1 To 8
  i = InStr(vWord, "|")
  If i = 0 Then
    Exit For
  Else
    vWord = Mid(vWord, i + 1)
  End If
Next

vName = getNextFld(vWord)
vAddr = getNextFld(vWord)
vAdd2 = getNextFld(vWord)
vAdd3 = getNextFld(vWord)
vAdd4 = getNextFld(vWord)
vAdd5 = getNextFld(vWord)
vAdd6 = getNextFld(vWord)
vPost = getNextFld(vWord)
vCtry = getNextFld(vWord)

vAddrBlock = vName & vbCrLf & vAddr & vbCrLf
If vAdd2 <> "" Then vAddrBlock = vAddrBlock & vAdd2 & vbCrLf
If vAdd3 <> "" Then vAddrBlock = vAddrBlock & vAdd3 & vbCrLf
If vAdd4 <> "" Then vAddrBlock = vAddrBlock & vAdd4 & vbCrLf
If vAdd5 <> "" Then vAddrBlock = vAddrBlock & vAdd5 & vbCrLf
If vAdd6 <> "" Then vAddrBlock = vAddrBlock & vAdd6 & vbCrLf
vAddrBlock = vAddrBlock & vPost & " " & vCtry

''MsgBox vAddrBlock
getAddViaBC = vAddrBlock
End Function


Private Function getNextFld(pvWord)
Dim i As Integer
Dim vRet

 i = InStr(pvWord, "||")
vRet = Left(pvWord, i - 1)
pvWord = Mid(pvWord, i + 2)
getNextFld = vRet
End Function

Hi ranman256
thanks for your prompt response it is very greatly appreciated.
I wlll get this VBA loaded into a module on the spreadsheet and let you know how I get on.
once again many thanks for all your help ranman256

regards

kevin

 

[Bassmank]

HI ranman256

once again what a great job you have done in trying to find a method to resolve this and much appreciated.

I have now included a mini sheet which can be changed as required to help with a layout and will upload a full working version in case any other users have similar issues in trying to extracting data from these barcodes.

I have loaded your code into a macro but it is showing an error when the code is run. (see vba image error below).

do you have any suggestions as to how to resolve this issue as I am merely a rookie when using vba code its just the basic stuff for me but getting better.

many thanks

Bassmank

POSTCODE EXTRACT MR EXCEL.xlsm
	A	B	C	D	E	F
1	SCANNED PDF147 BARCODES	BARCODE	FIRST ADDRESS	POST CODE	TRAY LOCATION NUM
2	[)1.3++T00H2A0080706427++614||001++UK16725593||++NDAY||||++Mr David Smith||20 Joe Blogs Street||East Calder||Livingston||||||||EH49 5SD||GB||+468930132517||0002||++1978||1||1||1||5333||GBP||++++004||570196||||||57||85++||++Boohoo||HEASANDFORD INDUSTRIAL ESTATE||WIDOW HILL ROAD||BURNLEY||LANCASHIRE||GB||||BB10 2BQ++27112021||(]				1
3	[)1.3++T00H2A0080706427++614||001++UK16725593||++NDAY||||++Mr David Smith||20 Joe Blogs Street||East Calder||Livingston||||||||EH49 5SD||GB||+468930132517||0002||++1978||1||1||1||5333||GBP||++++004||570196||||||57||85++||++Boohoo||HEASANDFORD INDUSTRIAL ESTATE||WIDOW HILL ROAD||BURNLEY||LANCASHIRE||GB||||BB10 2BQ++27112021||(]				2
4	[)1.3++T00H2A0080706427++614||001++UK16725593||++NDAY||||++Mr David Smith||20 Joe Blogs Street||East Calder||Livingston||||||||EH49 5SD||GB||+468930132517||0002||++1978||1||1||1||5333||GBP||++++004||570196||||||57||85++||++Boohoo||HEASANDFORD INDUSTRIAL ESTATE||WIDOW HILL ROAD||BURNLEY||LANCASHIRE||GB||||BB10 2BQ++27112021||(]				3
5
6
7
Sheet1

 

[ranman256]

you should post data so I can try. (not pictures of data)

 

[Fluff]

you should post data so I can try.

That's exactly what the OP has done.

 

[Bassmank]

Hi Ranman256

i have managed to get your code working now which is just great.

i am however receiving a few errors when the vba code is running.

one error is that no spaces exist between the address information.

I will post a screenshot of the errors shown shortly but as I said previously you have done a fantastic job and the code you have written is just fabulous.

kind regards

Bassmank