Deleting starting and trailing characters scanned into cell

glchat

New Member
Joined
Jun 11, 2012
Messages
3
I'm scanning data into a spreadsheet using a barcode scanner, but I want the starting character and trailing characters to be removed after I hit enter.

For example...let's say I'm scanning the following text in cell A1:

=W05281212345600

When I hit enter (or the barcode scanner does auto-enter), I want cell A1 to read:

W052812123456

I will always want the "=" and the last two zeros removed from the 16 character number, leaving the 13-character number in its place.

I tried using Excel's various truncate functions (LEFT,RIGHT,MID, etc.), but that only places the corrected text in another cell. That would be fine if I could copy that truncated text back over the original text...but that created a loop problem.

Any ideas?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
How is the column that the info gets scanned in to formatted? Is it formatted as text? If it is formatted as text, put this in the "ThisWorkbook" module in VBA.

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    
    If Left(Target, 1) = "=" Then Target = Mid(Target, 2, 13)
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,212,044
Messages
6,105,577
Members
447,972
Latest member
carrieann

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