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

Bassmank

New Member
Joined
Dec 30, 2021
Messages
4
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
paste this code into a module then the result can load into a cell: getAddViaBC()

Code:
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
 
Upvote 0
paste this code into a module then the result can load into a cell: getAddViaBC()

Code:
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()

Code:
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
 
Upvote 0
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).



Dim Ret error 2.png


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
ABCDEF
1SCANNED PDF147 BARCODESBARCODEFIRST ADDRESSPOST CODETRAY 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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,416
Messages
6,178,505
Members
452,853
Latest member
philipnjk64

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top