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

CaTrent

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).

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. 👍️