digest
Short Summary
Main recommendation: Choose the splitting method based on whether the job is one-time or ongoing and whether the data should update automatically. The video shows four ways to split one column of "ID date amount" text into three columns: Text to Columns and Flash Fill for quick one-time cleanups, Power Query for refreshable imports, and worksheet formulas for instant updates when the source changes.
Key Points
- The four methods shown to convert a single text column (ID, date, amount) into three columns are: 1. Text to Columns, 2. Flash Fill, 3. Power Query, 4. Excel worksheet formulas.
- Text to Columns is best for one-time cleaning events where the result does not need to update when the data changes.
- Flash Fill is fast when you can give one or two examples of the pattern and let Excel infer the rest.
- The video uses Power Query because the data is imported from an external text file, and clicking Refresh reruns the saved steps if the source changes.
- Worksheet formulas are the hardest method, but he says they are the only feature in Excel that updates instantly when the data changes.
Details Worth Noting
- Concrete instructions:
- Text to Columns: Select the column, go to Data tab > Data Tools > Text to Columns, choose Delimited, uncheck Tab, check Space (consecutive delimiters are treated as one), keep the General format so numbers and dates are interpreted correctly, choose a destination such as D5, and click Finish.
- Flash Fill: Type one or two examples of the desired output for each column, then go to Data tab > Flash Fill, or press Ctrl+E. For the date, if Excel gets the date right but not the format, press Ctrl+1 and apply the custom format mmm/dd/yyyy.
- Power Query: Go to Data tab > Get & Transform > From Text/CSV, select the file, choose Transform Data (not Load), set Use First Row as Headers, select the column, go to Home > Split Column > By Delimiter > Custom, enter three spaces, check data types (change the amount column to Decimal if needed), rename the columns, then use Home > Close & Load To to load into an existing worksheet as a Table. Click Refresh after the source file changes.
- Formulas, step by step: (1) ID: use =SEARCH(" ", cell)-1 to find the position of the first space minus one, then =LEFT(cell, that position) and add +0 to convert the text to a number. (2) Date: use =SEARCH("/", cell)-2 for the starting position, then =MID(cell, start, 10) and add +0 to convert it to a date serial number, then format as mm/dd/yyyy. (3) Amount: join the converted ID and date text with three spaces using TEXT for the date formatting, then use SUBSTITUTE to remove that text from the original cell, and add +0 to convert it to a number.
- The video notes that formulas must treat extracted text as text, so adding zero (or multiplying/dividing by one) is how to convert it back to a number or date.
- Text functions such as LEFT, RIGHT, and MID always return text, even when the content looks numeric.
- The formula approach is longer than the other methods; the presenter says a shorter alternative for the amount is to reuse the already extracted ID and date with SUBSTITUTE.
- The video does not state a specific source file format beyond a text file imported through Power Query.
Bottom Line
Use Text to Columns or Flash Fill for quick one-time splits, Power Query for repeatable imports you can refresh, and formulas only when the result must update instantly.
