Scroll To Top

Working with advanced Text Functions of Excel 2016

Posted in Excel1 year ago • Written by Ghulam AbbasNo Comments

Last time in the last lesson we had learned four text functions of Excel 2016 with step by step guide. But today I wanted to take your focus on more hard and sums of last functions. In reality, today I used combined those functions, and I got the result which I want. The whole purpose of this article is, don’t be afraid of combined functions, You can combine different functions. So I hope you will enjoy it.

Search and Left functions:

In last lesson you have learned left and search functions, also we had used first and last name combined and we found the result. So let’s take a look at other examples with Search and Left Functions. But first take a look at the screenshot.

Advanced Text Functions-Excel 2016

Advanced Text Functions-Excel 2016

In the screenshot, in the description, there is the product, region, and amount. So what I need is to extract all of them in their own potion. First, let’s start with Product.

Here is the complete function =LEFT(Description,SEARCH(“/”,Description)-2). First type the search function (=SEARCH(“/”,Description)-2) and press enter. It will show the product in number. Take a look at the screenshot.

Note: The reason why I subtract by 2 is, before the slash and after slash there is space, with the space it will be count 9 characters. So we don’t want to count, that’s why I subtract by 2.

Advanced Text Functions-Excel 2016

Advanced Text Functions-Excel 2016

In order to convert it to text, I will use left function. Press F2 on the keyboard to enable the formula in edit mode, before search type left and this function, and you will have the complete function which looks like =LEFT(Description,SEARCH(“/”,Description)-2), after that, you will have the answer. Take a look at the screenshot.

Advanced Text Functions-Excel 2016

Advanced Text Functions-Excel 2016

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

Search, Search, search and mid functions:

Search, Search, search and mid functions seem to silly but I couldn’t found any other name. If you have recognized in the description, the region is located in the middle of the texts. So let’s start with the search function.

1- First type this formula for search =search(“/”,Description)-2 and enter. 2- Before this function type this function =search(“:”,Description) and subtract it with last function, which we applied. which will look like this till this step:  =search(“:”,Description)-search(“/”,Description). 3- Before both search functions type this function, =MID(Description,SEARCH(“/”,C22)+2). Now take a look at the complete functions, also take a look at the screenshot especially the second one which I turned in edit mode.

=MID(Description,SEARCH(“/”,C22)+2, search(“:”,Description)-search(“/”,Description)-2

Advanced Text Functions-Excel 2016

Advanced Text Functions-Excel 2016

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

Right, Len and Search Functions:

We found the answer for Product and region, now it is time to find out the answer for the amount. On description list, you can recognize the prizes are listed at the end from the right side. IN order to find out the answer, first, type their the search function and search for the colon. Here take a look at the function. =search(“:”,Description).

Advanced Text Functions-Excel 2016

Search Function

It is time to find the exact number of amount. In order to find this, before search, type “Len” and apply this function. =len(Description). after this substrate it with the search to find out the exact number.

Advanced Text Functions-Excel 2016

Len-function

It is time to find out the value or the amount. In order to find it, before those two functions, apply right function. Here is the function: =right(Description, the wrist of the functions which we applied). Here is the full functions of right, Len and search: =right(Description,Len(Description)-search(“:”,Description). Take a look at the screenshot.

Advanced Text Functions-Excel 2016

right-Len-and-search-functions

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

Conclusion 

It was all about, Working with advanced 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 out the Clip:

TAGS: , , , , ,

Leave A Response