Excel Formulas – Text and Date Functions
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
Table Of Content
- Manupulating Data in Excel – formulas in excel
- Working with Text and Date Functions
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
Excel allows upto 255 Arguments to add in Excel.
As long has you dont delete any sheet which used for 3D formula calculation nothing affects the result.
You cannot undo Consolidation with link in Microsoft Excel.
