6 Tips to Work with Text in Microsoft Excel

1. Thinking “Outside of the Cell”

Many of the Excel users try to retyping or to manipulate data within the same cell in order to get the correct format. You have to think about the “outside of the cell” when you want to transform text in Excel. For this the excel sheet need the extra working columns to store the formulas to provide you the results that you need. so, if you have any kind of data in text form then there is no need to retype just use the following functions to recreate the data.

2. Changing Text Case

For example you have a data in A1 as shown above that has a mismatch of upper and lower case characters such as Chris Edwards then use the following formula.

In Cell B1 type = PROPER(A1) to change the initial value of the data to Caps

To change all the data to Caps then use the =UPPER(A1)

To make it same as original one then use the =LOWER(A1)

3. Removing Extra Spaces

It is very common for a data to have some space at the beginning or at the trailing, which will affect the search of the data and difficult to sort it. If you want to make shore that there is no space at the beginning or end of the data then use the TRIM function as =TRIM(A1). After applying this function the space which are given at the beginning or at the end will remove and you can easily sort the data. You can also use the TRIM function for other functions like =TRIM(PROPER(A1)).

4. Extracting Only What You Want

There is another entry problem when you want to some portion of a data entry. The below mention function will help you to find the character at the beginning, middle or at the end of the text entry. Take an example ABC2-45678-XYZ in the cell A1

Type =LEFT(A1,4) for the result “ABC2” of the first 4 characters from the left.

=RIGHT (A1,3) for the result of “XYZ” of the last 3 character from the right

To find the character in the middle portion then type =MID(A1,5,5)

5. Count the Characters in a Cell

Use the LEN function to count the length or number of character in the cell. Type =LEN(A1) it will determine the length of an entry in cell

6. Pulling Text Apart By Bringing Functions Together

In the excel there are some data which have the names addresses or other entries. So, it is no easy to find or sort it but with the help of this formula you can find it easily, e.g you have a name “John Peterson” in cell A1.

=LEFT(A1,FIND(” “,A1)-1) the result is “John” all of the characters to the left of the space

=RIGHT(A1,LEN(A1)-FIND(” “,A1)) the result is “Peterson” all of the characters after the space

All the above mentioned tips will help you to save your time form retyping text inMicrosoft Excel.

By: Muhammad Iqbal Akhtar