Automate Split Text to Columns, after row creation from form.

Options

I am creating a sheet for the purpose of inventory. On our items being inventoried they have a QR code that generated txt info. I want to split the txt into several separate columns.

Example of what is generated:

Model Number: AS3100

Size: L

Lot # 20-4186

Serial # 27233412

Level: IIIA

D.O.M. 06/2020


I want to auto-fill 6 different columns with only the individual info not the description of the info. Example:

In the Model Number column I want it to read AS3100

Thank you

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 02/03/23
    Options

    Hey @Ripj

    Is the format of the output in the cell identical to what you displayed? Line breaks are between each bit of information? A screenshot is always helpful.

    If this is exact, try these:

    Model Number

    =MID([QR Code Field]@row, FIND(":", [QR Code Field]@row) + 1, FIND(CHAR(10), [QR Code Field]@row) - FIND(":", [QR Code Field]@row))@Ripj

    Size

    =MID([QR Code Field]@row, FIND("~", SUBSTITUTE([QR Code Field]@row, ":", "~", 2)), FIND("~", SUBSTITUTE([QR Code Field]@row, CHAR(10), "~", 3)) - FIND("~", SUBSTITUTE([QR Code Field]@row, ":", "~", 2)))

    Lot#

    =MID([QR Code Field]@row, FIND("~", SUBSTITUTE([QR Code Field]@row, CHAR(35), "~", 1)) + 1, FIND("~", SUBSTITUTE([QR Code Field]@row, CHAR(10), "~", 6)) - FIND("~", SUBSTITUTE([QR Code Field]@row, CHAR(35), "~", 1)))

    Serial#

    =MID([QR Code Field]@row, FIND("~", SUBSTITUTE([QR Code Field]@row, CHAR(35), "~", 2)) + 1, FIND("~", SUBSTITUTE([QR Code Field]@row, CHAR(10), "~", 7)) - FIND("~", SUBSTITUTE([QR Code Field]@row, CHAR(35), "~", 2)))

    Level

    =MID([QR Code Field]@row, FIND("~", SUBSTITUTE([QR Code Field]@row, ":", "~", 3)), FIND("~", SUBSTITUTE([QR Code Field]@row, CHAR(10), "~", 9)) - FIND("~", SUBSTITUTE([QR Code Field]@row, ":", "~", 3)))

    D.O.M.

    =RIGHT([QR Code Field]@row, 7)

    Do these work for you?

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!