Flash-fill a column similar to Ctrl+E function in excel

I am looking for a solution similar to the Ctrl+E flash-fill feature in Excel, or a formula to extract just what I need into another column for export.

For example, I need a column that has information set up like "101 - Class Listing" to have a column lined up next to it that has the "101" extracted (all in the same row).

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

You could use a LEFT function to pull the 101 into another column.

CaTrent

Thank you, I've looked into the LEFT function here. What I need is the number to the left of the dash, but that could be 3-5 digits. Ultimately, I have to pull this into an Excel sheet for data transfer anyway but would love for this to be automated and ready to go when that happens.

101 - Class Listing 1 to 101

102 - Class Listing 2 to 102

102A - Class Listing 3 to 102A

102AB - Class Listing 5 to 102AB

Paul Newcome

In that case you could use a FIND function which outputs a number that is equal to the character number your "specific text" is found in the text string. So if you FIND " " (space) then that should output 4, 5, or 6 depending on where the first space is found. Using this number (minus 1) will tell the LEFT function how many characters to pull.

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

CaTrent

You, Paul, are fantastic. :) Thank you for explaining. This works perfectly!

Paul Newcome

Happy to help. 👍️

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.