What will learn from this post?

Think about the data with inconsistent data throughout and you need to perform some analysis on it. In Most of the business Analytics case studies, we have specific requirements to analyze the information processed during particular dates. Excel Formulas – Text and Date Functions has the power to work with text and Date Function to perform Data Manipulation which we will look at in this post

How to combine text in two cells on Excel?

We will be using the CONCAT function following = to perform combining two cells in Excel. A person can have 2 different approaches over here either use the CONCAT function or go with an ampersand. This will allow us to combine the two values in cells and along with that, we can manipulate the data with extra markings using Excel Formulas – Text and Date Functions.

=concat(cell1," ",Cell2)
or 
=cell1&"."&cell2            
For Email with first name and lastname =cell1&"."&cell2&"@gmail.com"

How to Make Lower and Uppercase inside a Excel?

you can use UPPER, LOWER, PROPER to manulpate your structure of Data, these are the forumulas in excel

• Proper – If you want a list of names to start with a capital letter can use Proper function in excel
• UPPER – Make all the words to the capital letter in the cell
• LOWER – Make cell to lower case.

=proper(concat(cell1," ",Cell2))
=lower(cell)
=Upper(cell)

How to Extract spesfic text from a cell in Excel

LEFT, MID, RIGHT to fetch or extract data from a cell, this will be more useful in the Data Wrangling phase before Data Analysis, exception case find the pattern of data and use the Find function to extract the data properly. We can use the find function as a helper in the above functions, Find finds the location of the character specified.

• LEFT – Helps to extract the data from the left
• MID – Extract the mid values from the cell, need to define starting and ending positions.
• RIGHT – Same like Left, it counts the pointers from the right side of the cell

=LEFT(text,num_chars)
=MID(text,num_chars_start,num_chars_end)
=RIGHT(text,num_chars)

=FIND("",D3)-4 //This will find the space in the text   and replace it over here =MID(text,num_chars_start,FIND("",D3)-4)

How to put current Date in Excel coloumns?

Users can use the =Now() function to implement the current system date and time, also =Today() function, and the function returns in a total number of days rather than years. If you want to get output in years use =YEARFRAC, cell, TODAY())

=Now() 
=Today()
=YEARFRAC,cell,TODAY())

FINAL VERDICT :

In conclusion, I hope you enjoyed reading this article on “Excel Formulas – Text and Date Functions”. In the next post, will learn Data Wrangling using Excel. How to make use of Business Intelligence with excel. Signing off Sanjay Kv

Tagged excel, Excel Tutorials, MS Excel, Tutorials