Split text into columns

Options

I'd like to split text into separate columns.

The text (found in one cell) has the following form: company name-document type-yymmdd-version number


E.g. I'd like to split Architecture-Proposal-221129-001 into four columns:

  1. Company Name
  2. Document Type
  3. Date
  4. Version

Appreciate any help or advice.

Thanks,

Jacob

Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @JBluestone

    I would have had this done quickly a couple hours ago but everyone always complains about helper columns and having to create/hide them. So I built the helper columns first and then slowly eliminated the helper columns by building their formulas into the main formula for each one. So that took some time and troubleshooting to get it right. You sort of get lost in all the LEFT/RIGHT nonsense and forget which helper formula you're on.

    Hope this helps!

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @JBluestone

    Here's the one for Company:

    =LEFT([Document Name]@row, FIND("-", [Document Name]@row) - 1)

    Here's the one for Document Type:

    =LEFT(RIGHT([Document Name]@row, LEN([Document Name]@row) - FIND("-", [Document Name]@row)), (FIND("-", RIGHT([Document Name]@row, LEN([Document Name]@row) - FIND("-", [Document Name]@row) - 1))))

    I'm working on the other two still. It gets much more complex for the last two.

  • JBluestone
    Options

    Thanks for the quick response. Is it easier if I eliminate the Document type column and just extract the Company Name, Date, and Version?

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @JBluestone

    No. It would only be easy if you only wanted the first two names extrapolated out. The further down the chain we get the harder the formula gets. If the FIND command allowed you to find 2nd, 3rd, 4th instance of being found, that would help.

  • JBluestone
    Options

    Oh, okay.

    It's not urgent but if you're able to help build that formula, I'd really appreciate your help.

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @JBluestone

    Date formula:

    =LEFT(RIGHT(RIGHT([Document Name]@row, (LEN([Document Name]@row) - FIND("-", [Document Name]@row))), LEN([Document Name]@row) - FIND("-", [Document Name]@row) - FIND("-", RIGHT([Document Name]@row, (LEN([Document Name]@row) - FIND("-", [Document Name]@row))))), FIND("-", RIGHT(RIGHT([Document Name]@row, (LEN([Document Name]@row) - FIND("-", [Document Name]@row))), LEN([Document Name]@row) - FIND("-", [Document Name]@row) - FIND("-", RIGHT([Document Name]@row, (LEN([Document Name]@row) - FIND("-", [Document Name]@row)))))) - 1)

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @JBluestone

    Version formula:

    =RIGHT(RIGHT(RIGHT([Document Name]@row, (LEN([Document Name]@row) - FIND("-", [Document Name]@row))), LEN([Document Name]@row) - FIND("-", [Document Name]@row) - FIND("-", RIGHT([Document Name]@row, (LEN([Document Name]@row) - FIND("-", [Document Name]@row))))), LEN(RIGHT(RIGHT([Document Name]@row, (LEN([Document Name]@row) - FIND("-", [Document Name]@row))), LEN([Document Name]@row) - FIND("-", [Document Name]@row) - FIND("-", RIGHT([Document Name]@row, (LEN([Document Name]@row) - FIND("-", [Document Name]@row)))) - FIND("-", RIGHT(RIGHT([Document Name]@row, (LEN([Document Name]@row) - FIND("-", [Document Name]@row))), LEN([Document Name]@row) - FIND("-", [Document Name]@row) - FIND("-", RIGHT([Document Name]@row, (LEN([Document Name]@row) - FIND("-", [Document Name]@row)))))))))

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @JBluestone

    I would have had this done quickly a couple hours ago but everyone always complains about helper columns and having to create/hide them. So I built the helper columns first and then slowly eliminated the helper columns by building their formulas into the main formula for each one. So that took some time and troubleshooting to get it right. You sort of get lost in all the LEFT/RIGHT nonsense and forget which helper formula you're on.

    Hope this helps!

  • JBluestone
    Options

    @Mike TV Thanks so much for your help with this.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!