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:

Company Name
Document Type
Date
Version

Appreciate any help or advice.

Thanks,

Jacob

Find more posts tagged with

Accepted answers

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!

All comments

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

Hope this helps!

JBluestone

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.