PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
Whenever we prepare any report in excel, we have two constituents in any report.
The Text portion and the Numerical portion.
But just storing the text and numbers doesn’t make the super reports. Many times we need to automate the process in the reports to minimize the effort and improve the accuracy.
Many functions are provided by the Excel which work on Text and give us the useful output as well. But few problems are still left on which we need to apply some tricks with the available tools.
In this articles , we are going to separate or split the text into different parts.
TEXT IS AN INACTIVE NUMBER TYPE[FORMAT] IN EXCEL. ANYTHING STORED AS TEXT [NUMBER OR DATE] WON’T RESPOND TO ANY STANDARD FORMULAS OR FUNCTIONS BUT SPECIALLY DESIGNED TEXT FUNCTIONS. [EXCEPTIONS DO OCCUR IN CASE OF NUMBERS]
SPLITTING/SEPARATING the text is also required many times in Excel.
This can be stated as removing any portion of the text or string from the cell and place it into the different cells.
Splitting and separating the cells can be done in a many ways. For example
We’ll discuss it here briefly as a complete detailed topic is already present. CLICK HERE TO CHECK OUT THE COMPLETE ARTICLE ON TEXT TO COLUMN.
In this section we will learn to separate any text into many portions even if there is no delimiter. [Of course if we’d be having delimiter we’ll be using Text to Column option].
We perform this action with the use of LEFT, RIGHT AND MID FUNCTIONS.
LEFT FUNCTION has the capability to pick the specified number of characters from the left side of the text.
RIGHT FUNCTION can extract the specified number of characters from the right whereas
MID FUNCTION takes out the specified number of the characters from the middle of the text.
Let us find out the way we can use these functions to extract the values from the text.
This problem occurs mostly when we have imported the data from somewhere and the date is in some format which doesn’t suit our system. The date can also be without any delimiter just like
11032011
12032011
13032011
and so on.
SOLUTION:
In this problem , first of all we have to check the pattern. We can see that the left two digits are the dates, after that the month and then the year.
If we want to put the date, month and year in the separate columns, put the following functions in the column for
DATE, MONTH AND YEAR.
FORMULA FOR DATE COLUMN =LEFT(CELL CONTAINING THE DATE,2) [PICKING 2 LETTERS FROM THE LEFT]
FORMULA FOR MONTH COLUMN =MID(CELL CONTAINING THE DATE,3,2) [STARTING FROM THE LETTER POSITION 3, PICKING 2 LETTERS]
FORMULA FOR YEAR COLUMN =RIGHT(CELL CONTAINING THE DATE,4) [PICKING 4 LETTERS FROM THE RIGHT]
The result will be the date, month and year separated and in three columns.
To further understand the usage of functions LEFT, RIGHT and MID, let us take one more example and check how we can use them.
Suppose we have the WEEKDAYS LIST and we need to limit the letters upto 4 only.
We can simply use the LEFT FUNCTION.
To Extract the 4 letters from the left of the weekdays we can use the formula as =LEFT(CELL CONTAINING WEEKDAY,4). It’ll extract the letters from the left as shown in the picture below.
If we look at the results, we can see that the function did what we wanted but as per standards , in a week we sometimes take 4 letters and sometimes 3 letters from the left. Let us refine the function and make it proper.
We know that the 4 letters are taken for TUESDAY ONLY. We can put an IF function in a way as
=IF(L4=”TUESDAY”,LEFT(L4,4),LEFT(L4,3))
If the day is TUESDAY , 4 letters would be taken from the left otherwise only 3 will be taken from the left.
The result is shown in the picture below which is better than the previous one.
THERE ARE MANY STANDARDS OF THE SHORT FORMS OF THE WEEKDAYS. THE MOTIVE HERE IS TO UNDERSTAND THE USAGE OF THE FUNCTION.
Similarly we can split any text if we know the position of the split.
Let us find out the way to split the text if we don’t know the position exactly.
Suppose we have a column with the first and last name put in the same cell.
If we want to apply any kind of formula on this, we need to separate or split the first and last name.
Suppose the data given is
Kim Rogers
Daniel Bros
Allison Berger
Joe Wilson
Ravi Kumar
Ashfaq Akhtar
We can do that using the following methods.
We can use this trick confidently and easily to separate the first and last name within seconds. [CLICK HERE TO LEARN TEXT TO COLUMN WITH PICTURES AND EXAMPLES].
After applying the text to column, we will be having the following data.
As already discussed we can use FLASH FILL also to separate the names into first name and last name in Excel.
[CLICK HERE TO LEARN FLASH FILL IN EXCEL]
STEPS TO USE FLASH FILL:
Attempt one sample manually. For our example Split the first row which is Kim Rogers as Kim and Rogers in the other column as shown in the picture.
After the automated options let us try now the manual one, of course using the functions.
Here are our requirements.
A function which can
With the use of these function , let us try to separate the text in Excel.
Let us try our same example first.
We just learnt how to separate the Names in two different cells. Now let us learn to separate the names which contain a Middle name into Three different portions.
STEPS TO SPLIT THE NAMES WHICH CONTAIN MIDDLE NAME TOO.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE