Split text into columns

JBluestone

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

Mike TV

@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!

Mike TV

@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

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

@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

Oh, okay.

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

Mike TV

@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

@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

@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

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