Page 211 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

Excel Flash Fill examples
As already mentioned, Flash Fill is a very versatile tool. The below examples demonstrate some of its capabilities,
but there is much more to it!
Extract text from cell (split columns)

Before Flash Fill came into existence, splitting the contents of one cell into several cells required the use of
the Text to Columns feature or Excel Text functions. With Flash Fill, you can get the results instantly without
intricate text manipulations.
Supposing you have a column of addresses and you want to extract zip codes into a separate column. Indicate
your goal by typing the zip code in the first cell. As soon as Excel understands what you are trying to do, it fills in
all the rows below the example with the extracted zip codes. You only need to hit Enter to accept them all.

Formulas to split cells and extract text
• Extract substring - formulas to extract text of a specific length or get a substring before or after a given
character.

• Extract number from string - formulas to extract numbers from alphanumeric strings.
• Split names in Excel - formulas to extract first, last and middle names.
Extracting and splitting tools
• Split Text - a versatile tool to divide cell content into multiple columns or rows. Easily split string by any
character, string, or mask.
• Split Names tool - fast and easy way to separate names in Excel.
Combine data from several cells (merge columns)
If you have an opposite task to perform, no problem, Flash Fill can concatenate cells too. Moreover, it can
separate the combined values with a space, comma, semicolon or any other character - you just need to show
Excel the required punctuation in the first cell:

Formulas to join cell values
• CONCATENATE function in Excel - formulas to combine text strings, cells and columns.
Merging tools
• Merge Tables Wizard - quick way to combine two tables by common columns.

• Merge Duplicates Wizard - Combine similar rows into one by key columns.

198

CITS : IT&ITES - Computer Software Application - Lesson 63 - 77