Ravindra BagaleCourses & study guides मराठी Track your progress

Excel Flash Fill (Ctrl+E) with Real Examples: Names, Emails, Phone Numbers and Masking

By Last updated:

Joining 500 first names and surnames, pulling the city out of 500 addresses, or hiding all but the last 4 digits of 500 ID numbers: by hand that is an afternoon of typing. With Flash Fill you type one example, press Ctrl+E, and Excel fills the rest by following your pattern. In this chapter we do more than fifteen real examples, see why long phone numbers turn into 9.19E+11, and learn when Flash Fill gets it wrong and how to correct it.

What you'll learn in this chapter

  • What Flash Fill is and how Ctrl+E works
  • Join first and last names, make initials and usernames
  • Build email IDs and split them into username and domain
  • Why long numbers show 9.19E+11, and how to keep them as text
  • Split a country code from a phone number
  • Mask ID numbers to show only the last 4 digits
  • Split product codes and pull the city from an address
  • Change case: UPPER and Proper
  • When Flash Fill fails, and how to fix it

Practice file: excel-flash-fill-practice.xlsx has one practice sheet and one answers sheet for every example.

1. What Flash Fill does

Why: a lot of everyday Excel work is reshaping text: joining, splitting, picking a part, changing case. Formulas can do it, but Flash Fill needs no formula at all.

What: you type the result you want for the first row. Excel looks at the data next to it, works out the pattern, and fills the rest of the column.

Classroom line

What Excel understands on its own and fills in for you, that is what we call Flash Fill.

How (first example: full name):

  1. Put the first names in column A and the surnames in column B: Ravindra Bagale, Shraddha Bagale and Ruhi Bagale.
  2. In C5, right next to the data, type the result for row 5: Ravindra Bagale. Press Enter.
  3. You are now in C6. Press Ctrl+E.
  4. C6 and C7 fill with Shraddha Bagale and Ruhi Bagale.

The same command is also in Data › Flash Fill and Home › Fill › Flash Fill. Sometimes Excel even shows a grey preview while you type the second row; press Enter to accept it.

Type one example, press Ctrl+E A B C 4 First name Last name Full name 5 Ravindra Bagale Ravindra Bagale 6 Shraddha Bagale Shraddha Bagale 7 Ruhi Bagale Ruhi Bagale 1 Type the result for the first row (C5) 2 Press Enter, then Ctrl+E 3 Excel fills C6:C7 with the same pattern Ctrl + E

Figure 1. Type one example (yellow), press Ctrl+E, and Excel fills the rest of the column (green) by following the pattern.

2. Rules Flash Fill needs

  1. Work right next to the data. The result column must touch the data columns. With an empty column in between, Excel shows a message that it didn't see a pattern.
  2. One output column at a time. Each new column needs its own first example.
  3. The results do not update. Flash Fill types plain values. If you later change a surname in column B, the full name in column C does not change; run Ctrl+E again (or use a formula when the data keeps changing).
  4. Check the result. Flash Fill guesses a pattern. Read a few rows, especially short and long names.
The result column must touch the data A B C 1 Ravindra Bagale Ravindra Bagale 2 Shraddha Bagale Shraddha Bagale 3 Ruhi Bagale Ruhi Bagale A B C D 1 Ravindra Bagale Ravindra Bagale 2 Shraddha Bagale 3 Ruhi Bagale Works: C is right next to A:B Doesn't work: empty column C in between Excel says it didn't see a pattern

Figure 2. Left: the result column touches the data, so Ctrl+E works. Right: an empty column in between, so Excel cannot see the data and shows a message instead.

3. Names: initials, usernames, emails and more

All of these start from the same two columns (first name, surname). Type the yellow example in row 5, press Enter, then Ctrl+E.

You type in row 5 Pattern Rows below become
rb initials, small letters sb, rb
r bagale first initial + surname s bagale, r bagale
ravindra_bagale username with underscore shraddha_bagale, ruhi_bagale
ravindra.bagale@example.com email ID shraddha.bagale@example.com, ruhi.bagale@example.com
ra ba first two letters of each sh ba, ru ba
Bagale, Ravindra surname first, with comma Bagale, Shraddha; Bagale, Ruhi
RAVINDRA BAGALE all capitals SHRADDHA BAGALE, RUHI BAGALE
Same names, seven patterns (yellow = typed example) Pattern Row 5 (typed) Row 6 Row 7 initials rb sb rb initial + surname r bagale s bagale r bagale username ravindra_bagale shraddha_bagale ruhi_bagale email ravindra.bagale@example.com shraddha.bagale@example.com ruhi.bagale@example.com first 2 + first 2 ra ba sh ba ru ba surname, first Bagale, Ravindra Bagale, Shraddha Bagale, Ruhi capitals RAVINDRA BAGALE SHRADDHA BAGALE RUHI BAGALE

Figure 3. One pair of name columns, seven different outputs. Each output column got one typed example and Ctrl+E.

Classroom line

If there is a pattern, Excel will understand it.

Ravindra Bagale's Tip

If Flash Fill picks the wrong letters, don't delete the column. Correct the first wrong cell by typing the right value there, press Enter, and Flash Fill updates the rest using the new example. Two examples remove most confusion.

4. Split an email ID

Flash Fill works in reverse too. Start from a column of emails such as shraddha.bagale@example.com:

  1. Username: type ravindra.bagale next to the first email, Ctrl+E.
  2. Domain: in the next column type example.com, Ctrl+E.
  3. Domain name only: in the next column type example, Ctrl+E.

This is useful when a list mixes company and personal emails and you want to count how many come from each domain.

5. Long numbers: why 919000000001 shows 9.19E+11

What happens: type a phone number with the country code, 919000000001, in a normal cell. Excel shows 9.19E+11. The number is still there, but the cell is in the General format, and a 12-digit number is too wide, so Excel shows it in scientific (E) form.

There is a bigger problem with very long numbers. Excel keeps only 15 digits of any number. Type a 16-digit card-style number and the last digit becomes 0, permanently.

Phone numbers, ID numbers, PIN codes and card numbers are labels, not amounts. You never add or average them, so store them as text:

  1. Before typing: select the column, press Ctrl+1, choose Text, click OK. Now everything you type keeps every digit.
  2. Or start the entry with an apostrophe: '919000000001. The apostrophe is not shown in the cell; it only tells Excel "this is text".

A text number sits on the left of the cell, and Excel may show a small green triangle ("number stored as text"). For a phone or ID column that is exactly what you want.

Long numbers: General vs Text A B C D 1 What General (number) Formula Bar shows Text column 2 Phone with 91 9.19E+11 919000000001 919000000001 3 ID number 1.23E+11 123456789012 123456789012 4 16-digit number 1.23E+15 1234567890123450 1234567890123456 General: a 12-digit number is too wide, so Excel shows E+ form. Only 15 digits are kept: the 16th became 0. Text (Ctrl+1 > Text before typing, or type an apostrophe first): every digit is kept, aligned left. Phone, ID, PIN and card numbers are labels, not amounts: store them as Text.

Figure 4. The same three numbers in a General column and in a Text column. General shows E+ notation and keeps only 15 digits; Text keeps every digit exactly as typed.

Ravindra Bagale's Tip

Set the column to Text before you type or paste the numbers. If you change it afterwards, a 16-digit number has already lost its last digit and the zero will not come back. For data you import, the phone numbers lesson shows how to clean existing columns.

6. Split the country code from a phone number

With the phone numbers stored as text in column A (919000000001, 919000000002 …):

  1. In B5 type 91, press Enter, then Ctrl+E. The country codes fill.
  2. In C5 type 9000000001, press Enter, then Ctrl+E. The 10-digit numbers fill.

The same idea joins them back: two columns 91 and 9000000001, type +91 9000000001 in the next column, Ctrl+E.

7. Masking: show only the last 4 digits

Why: when you share a list with ID numbers or card numbers, the other person usually needs only enough to recognise the record, not the full number. Showing the last 4 digits is the common way.

How:

  1. Column A has 12-digit ID numbers stored as text, for example 123456789012.
  2. In B5 type ********9012 (eight stars, then the last four digits of A5). Press Enter.
  3. Press Ctrl+E. Every row shows eight stars and its own last 4 digits.

Classroom line

We want to show only the last 4 digits, not the rest.

Show only the last 4 digits A B 4 ID number (Text) Masked 5 123456789012 ********9012 6 111122223333 ********3333 7 222233334444 ********4444 8 333344445555 ********5555 9 444455556666 ********6666 10 555566667777 ********7777 Before you share the file Delete column A, or copy only column B into a new workbook. A hidden column can be unhidden by anyone.

Figure 5. Masking with Flash Fill: one typed example with eight stars and the last 4 digits, then Ctrl+E for the rest.

Ravindra Bagale's Tip

Masking adds a new column; the full numbers are still in column A. Before you share the file, delete column A (or copy only the masked column into a new workbook). Hiding the column is not enough, because anyone can unhide it.

8. Split codes and pull a part of an address

Product codes. Column A has codes like ELEC-01, FURN-02, TOYS-03:

  1. Type ELEC next to the first code, Ctrl+E: the category fills.
  2. In the next column type 01, Ctrl+E: the number part fills. Format this column as Text first if you want to keep the leading 0.

Addresses. Column A has addresses like Flat 4, Shivaji Nagar, Pune, India:

  1. Type Pune next to the first address, Ctrl+E: the city fills.
  2. In the next column type Shivaji Nagar, Ctrl+E: the area fills.

This works well when every address has the same structure, with the parts separated by commas in the same order. If one address has an extra part, check that row and correct it by hand.

Split at a dash, pick a part between commas A B C 1 Product code Category Number 2 ELEC-01 ELEC 01 3 FURN-02 FURN 02 4 TOYS-03 TOYS 03 A B C 1 Address City Area 2 Flat 4, Shivaji Nagar, Pune, India Pune Shivaji Nagar 3 House 12, Gandhi Road, Nashik, India Nashik Gandhi Road 4 Room 7, Station Road, Solapur, India Solapur Station Road Yellow = typed example Green = Ctrl+E Works when every address has the same parts in the same order

Figure 6. Flash Fill splits a product code at the dash, and picks the city or the area from an address, as long as the parts are separated by commas in the same order.

9. Change case

Names often arrive in mixed case: ravindra bagale, RUHI BAGALE.

  1. Proper case: next to ravindra bagale type Ravindra Bagale, Ctrl+E.
  2. Capitals: in the next column type RAVINDRA BAGALE, Ctrl+E.

Formulas do the same job and also update when the source changes: =PROPER(A5), =UPPER(A5), =LOWER(A5).

10. When Flash Fill gets it wrong

  1. Different lengths. Asked for ravi b from Ravindra Bagale, Excel may guess "first 4 letters" or "letters up to the v". A short name such as Ruhi shows which one it chose. Give a second example in a row where the name length is different.
  2. Another column confuses it. If a column on the left also contains text, Excel may take letters from the wrong column. Type the correct value in the first wrong row, Enter, and Ctrl+E again.
  3. Uneven data. Addresses with a missing comma, or codes with no dash, break the pattern for that row. Check those rows by hand.
  4. Nothing happens. Make sure the result column touches the data and the cell you are in is directly below your example.

11. The whole chapter on one page

  1. Type one example next to the data, Enter, Ctrl+E. Also in Data › Flash Fill.
  2. The result column must touch the data; one output column at a time.
  3. Flash Fill writes values; they do not update when the source changes.
  4. Names: full name, initials, usernames, emails, "surname, first", capitals.
  5. Emails split into username, domain and domain name.
  6. Long numbers: General shows 9.19E+11 and keeps only 15 digits; set Text (Ctrl+1) or type an apostrophe first.
  7. Phone: split 91 from the 10-digit number.
  8. Masking: ********9012 + Ctrl+E; delete the full numbers before sharing.
  9. Codes split at the dash; addresses split at commas when the structure is the same.
  10. Wrong results: correct the first wrong cell and press Ctrl+E again.

12. Try at home

Try at home

Task 1: names

  1. Type first names and surnames for three people (Ravindra Bagale, Shraddha Bagale, Ruhi Bagale).
  2. Make full names, initials, first.last usernames and first.last@example.com emails, one column each.

Task 2: phone numbers

  1. Set a column to Text and type six numbers from 919000000001 to 919000000006.
  2. Split each into 91 and the 10-digit number.
  3. In a General column, type one 12-digit number and see the E+ form.

Task 3: masking

  1. Type six 12-digit ID numbers (as text), such as 123456789012.
  2. Mask them to show only the last 4 digits.
  3. Copy only the masked column into a new workbook.

Task 4: practice file

  1. Open excel-flash-fill-practice.xlsx.
  2. Do each practice sheet, then compare it with its "(answers)" sheet.

Recap

In short

  1. Flash Fill = one example + Ctrl+E.
  2. Work in the column right next to the data.
  3. Results are fixed values; rerun after changes.
  4. Store phone, ID and card numbers as Text: Excel keeps only 15 digits of a number.
  5. Mask with stars + last 4 digits; remove the full column before sharing.
  6. Split codes, emails and addresses when the structure is consistent.
  7. A second example fixes most wrong guesses.

Quick revision cards

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

Revision card: Flash Fill Q&A. Covers How does it work?; Does it update?; Why 9.19E+11 for a phone number?; It guessed wrong?. With a photo of Ravindra Bagale and a short tip.
Card 1 of 2: Flash Fill Q&A · Download PNG
Revision card: Flash Fill examples. Covers Shraddha Bagale; Ruhi Bagale; salman@mail.com; 9876543210; PUNE. With a photo of Ravindra Bagale and a short tip.
Card 2 of 2: Flash Fill examples · Download PNG

More cards for every chapter: Ravi Sir's Cards.

Loading practice tasks...

  1. Total nights (Easy): Add up Nights in D2:D11. (sheet "Data")
  2. Average rate per night (Easy): Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Standard (Easy): How many rows have Room type "Standard"? (sheet "Data")
  4. Largest nights (Easy): The largest value in D2:D11. (sheet "Data")
  5. Clean name (Easy): Trim A2. (sheet "Text")
  6. Proper case (Easy): A2 trimmed and in proper case. (sheet "Text")
  7. Code prefix (Easy): First 3 characters of B2. (sheet "Text")
  8. Code number (Easy): Last 4 characters of B3. (sheet "Text")
  9. Nights for Standard (Medium): Total of D2:D11 where B is "Standard". (sheet "Data")
  10. Standard in Leeds (Medium): Total of D2:D11 where B is "Standard" and C is "Leeds". (sheet "Data")
  11. City part (Medium): Characters 5 to 7 of B4. (sheet "Text")
  12. Name length (Medium): Length of A3. (sheet "Text")

See all practice tasks in the Task Hub →