Scroll To Top

Text Functions of Excel

Posted in Excel11 months ago • Written by Ghulam AbbasNo Comments

Microsoft Excel which has different functions used to calculate so hard and complex sums and questions. BUt without calculating mathematics It has so 23 function for date and time, at the same it has so many functions for text ( It means it converts numbers to alphabets).  SO today I will show 4 Text Functions of Excel 2016 with step by step guide. I hope you will enjoy this article.

The functions which I am using in today’s topic:

  1. Left function is used to extracts a given number of characters from the left.
  2. Right Function is used to extracts a given number of characters from the right.
  3. Search function tells you the starting position in a text string of text you specify. FIND is similar to SEARCH, but it is case sensitive.
  4. Len function is used to counts characters.
  5. Join

 

Join Function:

Since the name of join comes in, it means you have to join something. IN mathematics for joining two or more characters we are using addition, subtraction, multiply, division. Although texts don’t have addition, subtraction, multiply, division but instead we can join two names, two texts. Take a look at the example in the screenshot.

Text Function of Excel

table-of-join

In this table, you need to combine the first name, space, and last name. In order to join them with the function type this function ( =First name&” “&last name ) and press enter you can both of them are combined. Select it, and double click to fill down the table.

Note: This “&” sign is the sign of combination for text. Inverted ( ” “ ) commas with no fill is the sign of space.

Note: I have shown the 2nd formula in the screenshot so you have no problem while applying the function.

text functions of excel

result-of-join

If you have any problem while applying this function, please ask me. I will be happy to answer you.

Search Function:

 In Join function I had show you how to combine first name, space, and last name. But with the search function, I will show you how to separate first name, space, and last name.

In order to separate it, first, we need to change the first name to numbers, after that we will change that to text. So here is the of search =search(” “, name)-1 . Now take a look at the screenshot.

Note: The reason why I subtract that with -1 that it shouldn’t count space.

text functions of Excel

search-function

Now it is time to change the number to text. In order to change to text, I am using left function. Click F2 on the keyboard on any functions which we applied for the names to show the formula. After that apply this function. =LEFT(Name,SEARCH(” “, Name)-1) which clearly shown and highlighted in the screenshot.

left-search-combination

Text Function of Excel

If you have any problem while applying this function, please ask me. I will be happy to answer you.

How to take out the Last name from the name? It is almost the same as first name, you have to apply this function  RIGHT(Name,LEN(Name)-SEARCH(” “,Name)). First, apply search function then and at last, apply right function and you will the answer.

Note: I have highlighted the new functions for you so you shouldn’t face any problem while applying the functions.

search-len-right-combination

Text Function of Excel

If you have any problem while applying this function, please ask me. I will be happy to answer you.

Left Function: Left Function  extracts a given number of characters from the left. In order to learn it more advanced take a look at the screenshot.

table-of-state-and-zip

Text Function of Excel

In this table I have given to you state and zip code together, what I have asked from you is to separate the state and zip code in the blank table in front of them. In order to get the answer, go to state blank and type this function =left( state-Zip, 2). The reason why I have added 2, because I want 2 characters to show be an extract from the left so it should show, the only state not zip. Here take a look at the screenshot for better understanding while applying this function.

left-function

Text Function of Excel

Right Function: Right function is also the same as the left function it only extracts a given number of characters from the right. So I will apply right function for feeling the zip area. Type this function =right ( State-Zip, 5 ). The reason Why I added 5, that it should separate 5 characters from the right.  For better understanding take a look at the screenshot.

right-function

Text Functions of Excel

If you have any problem while applying this function, please ask me. I will be happy to answer you.

Conclusion 

It was all about, Text functions of Excel 2016. I hope you have learned this article, I hope you will ask your questions, give us your suggestions, opinion about what articles we have to write. If you faced any problem tell us below by comment, Feel free to tell us. we’re waiting for your suggestion.

Watch the clip for better understanding:

TAGS: , , , , , , ,

Leave A Response