FormulaCamp
Completed
6. Data Management and Cleaning · Lists and Messy Data · 20 min

7. Flash Fill and Text Formulas

Lesson 7 of 10
Task 1 of 5

  1. In D2:D6, extract the first name: the text before the first space.

Lesson theory

Flash Fill (Data › Flash Fill, Ctrl+E) guesses a pattern from one or two examples: you type "Emma" next to "Emma Rose Johnson", press Ctrl+E, and Excel fills in the rest of the first names.

The downsides: the result is plain values, not formulas. If rows are added or a name is corrected, you have to do it again. Excel also sometimes guesses the pattern wrong, so always check the result.

Formulas do the same job reliably and recalculate on their own:

  • FIND(" ",A2) is the position of the first space, FIND(" ",A2,FIND(" ",A2)+1) is the position of the second;
  • LEFT, MID and RIGHT cut out pieces, and & joins them;
  • SUBSTITUTE(B2," ","") removes spaces, and LOWER makes letters lowercase.
In Excel 365 it is easier: TEXTBEFORE(A2," ") is the text before the space, and TEXTAFTER is the text after it.

Practice is part of Pro

The live spreadsheet, task checking and .xlsx files in all 170 lessons. Your progress is saved in this browser; sync across devices is coming soon.

Get ProFree lessons