Hi All,

A co-worker approached me to help make a macro he received function automatically, and quicker.

The macro parses out the data from a magnetic strip from a license swipe. Basically taking the raw data with its symbols and putting each piece of data into its own cell.

I've cleaned up the code a bit (clearly made with a macro recorder) and made it so that if you select a cell in Column A and swipe a card, the other cells populate.

What I'm stuck on is this:

When the license is swiped, the desired information is put into the cell (lets say A2). However, the card reader pastes additional information in A3. This includes the birdthdate and other info that isn't necessary or wanted for our purposes.

The string in A3 always starts with a ";". I've tried for a while now to write code that will clear this cell before the rest of the macro runs, but I've hit a wall.

My code is below, if you have suggestions for making it more efficient or can solve my dilemma, that would be wonderful!

Function piece(Searchstring As String, Separator As String, IndexNum As Integer) As String
Dim t
t = Split(Searchstring, Separator)
If UBound(t) > 0 Then piece = t(IndexNum - 1)
End Function

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rownum As Integer
Dim dchr As Variant

Application.ScreenUpdating = False

If Not Intersect(Range("a2:a1000000"), Target) Is Nothing Then
           rownum = Target.Row
           Else: Exit Sub
    End If
Cancel = True

'On Error GoTo errhandler

dchr = Left(Range("a" & rownum).Offset(1, 0), 1)

If dchr = ";" Then
ActiveCell.ClearContents
End If

Range("a" & rownum).TextToColumns Destination:=Range("b" & rownum), DataType:=xlDelimited, _
        TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="%", FieldInfo:=Array(Array(1, 9), Array(2, 2)), TrailingMinusNumbers:=True

Range("b" & rownum).TextToColumns Destination:=Range("G" & rownum), DataType:=xlDelimited, _
        TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="^", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 9)), _
        TrailingMinusNumbers:=True

Range("G" & rownum).TextToColumns Destination:=Range("k" & rownum), DataType:=xlFixedWidth, _
        OtherChar:="^", FieldInfo:=Array(Array(0, 2), Array(2, 2)), _
        TrailingMinusNumbers:=True

Range("H" & rownum).TextToColumns Destination:=Range("N" & rownum), DataType:=xlDelimited, _
        TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="$", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 9), Array(4, 1)), _
        TrailingMinusNumbers:=True
      
Range("p" & rownum).Value = Date

Range("q" & rownum).Value = Time

'errhandler:

Application.ScreenUpdating = True

End Sub