Page 213 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

Replace part of cell contents
Replacing part of a string with some other text is a very common operation in Excel, which Flash Fill can also
automate.
Let's say, you have a column of social security numbers and you want to censor this sensitive information by
replacing the last 4 digits with XXXX.
To have it done, either use the REPLACE function or type the desired value in the first cell and let Flash Fill auto
fill the remaining cells.

Advanced combinations

Flash Fill in Excel can accomplish not only straightforward tasks like demonstrated in the above examples but
also perform more sophisticated data re-arrangements.
As an example, let's combine different pieces of information from 3 columns and add a few custom characters to
the result.
Supposing, you have first names in column A, last names in column B, and domain names in column C. Based on
this information, you want to generate email addresses in this format: initial.surname@domain.com.
For experienced Excel users, there is no problem to extract the initial with the LEFT function, convert all the
characters to lowercase with the LOWER function and concatenate all the pieces by using the concatenation
operator.
=LOWER(LEFT(B2,1))&"."&LOWER(A2)&"@"&LOWER(C2)&".com"

200

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