You know the scenario, you need to separate a column of full names into first and last name. What's more is that the column of full names is over 100 long. What do you do?
Using Flash Fill to split data makes this process is quite straightforward. Want to give it a go? Do the following:
There you have it amazing, you can also use the short-cut key Ctrl & E
Now we know that various countries around the world have various ways of displaying dates. But one of the most often encountered variation is the US/UK date divide. Basically, the US would display the 22nd of March 2020 as 03/22/2020. Whereas in the UK it would be displayed as 22/03/2020.
While neither of these is right or wrong, it is important to be consistent. Knowing how to convert US to UK dates using Flash Fill in Excel is one little trick you don't want to miss.
Flash fill is a great way to automatically create email addresses from names. So long as there is a consistent pattern to the email. For example, if the company email address consisted of first name, then a period, then last name Flash Fill in Excel will have no problem bringing the names together in an email address.
In addition to the 3rd point above. If Flash Fill should fail to bring together the email address in the correct way, you can correct the first occurrence. After that Flash Fill should automatically update the others. You may have to do this more than once, the more you make corrections the more Flash Fill can recognise the pattern.
We often see dates entered in the incorrect format. This has either been entered incorrectly, or the date has been produced by a 3rd party database. However which way it happens if your date is the following format:
Excel won't recognise the cell as a date; it will be classed as text. That being the case it means that all of those wonderful date calculations such as extracting the day of the week or month of the year won't work. What's more you will be unable to calculate the length of time between dates.
When entering a date in Excel it should be in the following format:
So you want to know how to correct date format in Excel? Then Flash Fill can correct the date for you:
Sometimes you might want to add text together in Excel. Using Flash Fill this is possible. You might what to do this if you want to create a list of addresses.
It's amazing at how many people that use Excel overlook the Flash Fill feature. Whether they're looking to convert date formats or converting US date to UK Flash Fill makes the complicated easy. I especially love the short-cut Ctrl & E when using Flash Fill, it makes you look so professional. When you create an email address from a name in Excel, using Flash Fill makes this child’s play. Whether you want to create an email address from first and last name, or if you want to take into account the company name, Flash Fill will make it easy to convert text to email addresses in Excel.
Flash Fill is a fantastic tool in Excel and one that is often overlooked. Make sure that you know how to do Flash Fill in Excel to make your data entry that little bit easier.
Other Excel Videos Tutorials