So I work as a student employee at my college and part of my job is to check people in and out. I've been doing this manually by looking at their ID and writing the information, but I am trying to find a way to use ID swipes on a magstrip reader to streamline the process. When I swipe a school ID on the reader, it'll type an entry like:


Where the bolded part is the actual ID number and everything else isn't relevant (at least to me). I found an easy solution using a python program just to turn it into a string, get rid of the semicolon and the question mark, and then use math to remove everything except for the ID number. Is there any way in google sheets to just have them swipe their ID, the Raw ID data get inputted into a cell, and then using some kind of function/other feature to just isolate the bolded ID code?

Another idea that I think is less likely to be possible is I'd want to find a way to link the ID number and their name. That way I don't have to have them swipe their ID number and then manually type in their name too. I wish I knew of a way that they could just swipe their ID and it would automatically input all the information I need since the current process is extremely inefficient.


  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community
    Sep 14 at 4:02
  • Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. Sep 14 at 4:39

Assuming that ;21944931924914916932586267152894? is in cell A2, you can extract the nine characters that comprise the ID, starting at the seventh character, with this formula:

=mid(A2, 7, 9)

To automatically do the same with all values in the column A2:A, use this array formula in row 2 of a free column:

=arrayformula( mid(A2:A, 7, 9) )

To find the name that corresponds with an ID, use the vlookup() function.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.