Barcode Scan and separate data into respective columns
 

I'm trying to scan a barcode. I have multiple entries in a single cell that I
want to separate into their respective columns. For example the format of the
barcode scan is "123-454-345 r2.1 7012495" Each entry is separated by a space
and I need each entry to be put into their designated columns. After I
scanned the barcode, I used the Text to Column tool which gives me the result
I want, but I don't want to do this for every entry. Is this possible to
create some formula, function, or macro to accomplish my needs? I need help
please!!!

Answer: Barcode Scan and separate data into respective columns
 

Yes, it is possible to create a formula or a macro to separate the data into respective columns. Here's how you can do it using a formula:

1. Assuming that the barcode scan is in cell A1, insert the following formula in cell B1:
   
   
   Formula:

   ---

   =LEFT(A1,FIND(" ",A1)-1) 


   ---

   This will extract the first entry (123-454-345) from the barcode scan.
   
2. Insert the following formula in cell C1:
   
   
   Formula:

   ---

   =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1) 


   ---

   This will extract the second entry (r2.1) from the barcode scan.
   
3. Insert the following formula in cell D1:
   
   
   Formula:

   ---

   =RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)) 


   ---

   This will extract the third entry (7012495) from the barcode scan.
   

Now you have each entry in their designated columns. You can copy and paste these formulas down to the rest of the rows to separate the data for all the entries.

Alternatively, you can create a macro to automate this process. Here's how:

1. Press Alt + F11 to open the Visual Basic Editor.
   
2. Click on Insert Module.
   
3. Insert the following code:
   
   
   Formula:

   ---

   Sub SeparateData()
    Dim cell As Range
    For Each cell In Selection
        cell.TextToColumns Destination:=cell, DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, _
        Tab:=False, Semicolon:=False, Comma:=False, Space:=True, _
        Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
    Next cell
End Sub 


   ---

4. Press F5 to run the macro.
   
5. Select the range of cells that contain the barcode scans.
   
6. Run the macro.
   

This will separate the data into respective columns for all the entries in the selected range.

Barcode Scan and separate data into respective columns
 

One way to do things would be to use formulas in Excel to extract the
data and put it into the correct cells.
For example, suppose that your data is:
123-454-345 r2.1 7012495
I assume that there are three fields:
123-454-345
r2.1
7012495

I also assume that the data encoded in the bar codes in the first two
fields will always be the same length - if it is not fixed length (at
least the first two fields) then the following technique will not
work.

If you scan the above data in cell A1 and then put the following
formulas in B1, C1 and D1 then you should get what you want:

Cell B1 formula: =LEFT(A1,11)
Cell C1 formula: =MID(A1,13,4)
Cell D1 formula: =MID(A1,18,20)

Another approach that you could take is to reprogram your bar code
scanner to convert the space characters to TABs. I assume that your
bar code scanner has a "keyboard wedge" interface so that it enters
the data as keystrokes into whatever application has the input focus.
Most keyboard wedge scanners come with a programming manual that
contains a number of special bar codes that you can scan to program
how the scanner works. If your scanner can be programmed to translate
individual characters to specific keystrokes then you should be able
to translate the spaces in the bar codes to tab keystrokes so that the
data would get appear in Excel in separate columns.
If your bar code scanner has a RS232 output then you must be using a
"software wedge" to input the data into Excel. If this is the case
then you could check the settings in the software to see if there is a
way to translate spaces to tabs. If you are using WinWedge from TAL
Technologies (www.taltech.com) then there is a translation table in
WinWedge that you can use to translate spaces to tabs.


On Wed, 4 Apr 2007 15:04:01 -0700, ExcelNewbie
wrote:

123-454-345 r2.1 7012495

Barcode Scan and separate data into respective columns
 

Hi,

If your bar code scanner has a RS232:

To input the data in your Excel, you can use the software
"BillRedirect" price only 35$ USD
*There a free DDE Excel Plugin that creates a real-time connection
between BillRedirect and your Microsoft Excel.

In this software section Search and Replace you can translate spaces
to tabs.

You can Download and install the free Demo version to test prior to
purchasing the full version:

http://www.BillProduction.com/