Removing a character from a barcode string

justinm1015

New Member
Joined
Apr 27, 2009
Messages
13
I use a USB barcode scanner which reads data in a barcode label and enters it into a cell in Excel. The barcodes all contain seven characters.

The barcode lablels all have the following format: A318639....that is a letter followed by six numbers.

When I scan the barcodes into any cell, a dollar sign "$" appears in the front of the barcode string that is entered into the cell. The dollar sign is not a relevant part of the string of characters in the barcode label.

What I am looking for is a solution where upon scanning a barcode into any cell, the dollar sign is removed from the front of my string and I am left with just the seven character string which is shown above.

I have experimented with the Data Validation tool by trying to limit the number of characters that are allowed in a cell to seven characters, but that has not actually removed any characters from my data string.

Any ideas? Thanks in advance for your wisdom.

Justin M.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
justinm1015,

We could use the Worksheet_Change Event in the worksheet you have the barcodes entered into. It will check the cell, and remove the leading "$" character, as you read the barcodes in.

What column are the barcodes in?

What is the first row in the column that the barcodes are in?

Can we see more examples of the barcodes you scan in.
 
Upvote 0
In the column next to the column where barcodes appear enter formula

=value(cell where barcode is)
 
Upvote 0
In the column next to the column where barcodes appear enter formula

=value(cell where barcode is)
Wouldn't that return a #VALUE error as these barcodes are alphanumeric and not necessarrily numeric?

Is there a reason teh $ sign needs to be removed at the time of the barcode is being scanned in and not at the end of the scanning process?
 
Upvote 0
justinm1015,

We could use the Worksheet_Change Event in the worksheet you have the barcodes entered into. It will check the cell, and remove the leading "$" character, as you read the barcodes in.

What column are the barcodes in?

What is the first row in the column that the barcodes are in?

Can we see more examples of the barcodes you scan in.

Hiker,

The column that the barcodes are in is column E.

The first row that the barcodes are in is row 2....I am using row 1 as a header row.

Some more barcode examples: A126342, A254849, A244859.

To date I have only been working with barcodes that begin with the letter "A". In the future it is possible that I will be given barcodes that begin with another letter, but will have the same format (letter followed by six numbers.)
 
Upvote 0
Wouldn't that return a #VALUE error as these barcodes are alphanumeric and not necessarrily numeric?

Is there a reason teh $ sign needs to be removed at the time of the barcode is being scanned in and not at the end of the scanning process?

Schielrn,

I'm not sure exactly what you mean by "end of scanning process" but this is what the process is as far as I can see.

1. Place cursor in cell
2. Scan barcode which puts value in cell
3. Press the "tab" key or use my mouse to move to another cell

So I guess that #3 could be considered the end of the process.
 
Upvote 0
I just mean that you can use find and replace to find $ and replace with nothing. That would replace all the $ signs. I was just saying that one time after you have done all of your scanning you can do one replace all.

Hope that makes sense of what I was after.
 
Upvote 0
I just mean that you can use find and replace to find $ and replace with nothing. That would replace all the $ signs. I was just saying that one time after you have done all of your scanning you can do one replace all.

Hope that makes sense of what I was after.

Schielrn,

Thanks!

I never even thought of using find and replace; it's the answer to my problem. The scanning instance is the only time when a "$" sign is used in the entire worksheet so I can find all "$" and replace with nothing not having to worry about deleting a dollar sign that I might need.

Overcomplicated like usual..............
 
Upvote 0
justinm1015,

Sorry for the delay.

Sample data in column G for me to copy and past into column E:


Excel Workbook
EFG
1Barcode
2$A126342
3$A254849
4$A244859
5
Sheet1



As I manually copy data from column G, and paste into column E, to simulate you scaning the barcodes:


Excel Workbook
EFG
1Barcode
2A126342$A126342
3$A254849
4$A244859
5
Sheet1



Excel Workbook
EFG
1Barcode
2A126342$A126342
3A254849$A254849
4A244859$A244859
5
Sheet1




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Right click the sheet tab you want the code in, and click on View Code. Paste the below code there (on the right pane) by pressing the keys CTRL + V
4. Press the keys ALT+Q to exit the Editor, and return to Excel

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E2:E10000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  If Left(Target, 1) = "$" Then Target = Right(Target, Len(Target) - 1)
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub


Now try scanning as usual.
 
Upvote 0
justinm1015

Could you not just use the mid function? If all scans go into the same column say G put the mid formula in same row in cloumn E. =MID(G1,2,7). drag the formula down in E.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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