Parse data from the middle of a text string

  • 0
  • 1
  • Question
  • Updated 4 weeks ago
  • Answered
I am setting up bar coding in an app where I will scan a QR code. The code has the following string of information. I need to put the model number in one field and the serial number in another. I can get part of this to work but sure there may be a better way.

Here is the text string from the scan and is always the same for our equipment. I will probably have to setup some other rules for other vendors. I am using an Equipment Manufacturer drop down so I will be able to use that as a trigger for future rules as this expands and I test other bar codes.

https://qr.hussmann.com?serialNumber=MY18D023685&modelNumber=IM4SL12

Photo of Joey Zint

Joey Zint

  • 1,870 Points 1k badge 2x thumb

Posted 4 weeks ago

  • 0
  • 1
Hi Joey,

I did some experimenting with formula text fields using the Left and Right functions and came up with something that might work for you. It might not be the most elegant solution, but it does work.

The solution utilizes 4 fields including the the field which contains the string we want to parse and 3 other fields to store the parsed values. (You will want to hide the Trimmed Serial String field from end users and exclude it from reports and filtering.)

Create three formula text fields as follows:

  1. [Model Number]  enter this formula: Right([Source String], "=;")
  2. [Trimmed Serial String] enter this formula: Left([Source String], "&;")
  3. [Serial Number] enter this formula: Right([Trimmed Serial String], "=")


Photo of Joey Zint

Joey Zint

  • 1,870 Points 1k badge 2x thumb
That worked, thanks. I will have to develop more "what ifs" for other equipment but this worked for ours.