Hello everyone. In this video session we will be talking about substitute and replace formulas and how sometimes they can help pull relevant information from the given data. Now substitute is generally used when you want to replace a specific instance of a character. For example, I want to replace all the spaces with maybe hyphens or maybe Kowloon, wherever they exist in the data. I may also want that Excel must identify the first instance of space and replace that with let's say a hyphen or Kowloon, not the subsequence instances of space. Now, how do you substitute all the hints are provided as a screenshot, let me decipher them.

So I type in su B and from the drop down, I can press the Tab key to fully completed. Now the first option first parameter hint tells me choose the cell choose the text in which you want to manipulate the data. So that's the one Which I have chosen Tony, comma. Now you must tell Excel what is the current character that you would want to replace with a new character. So, within double quotation, a space double quotation closed. Now this can be any letter any any numeric character, any space any special character.

So that's the old text, which I would want to substitute for new text. So the new text the third option tells me to put in the new text. So within double quotation, the colon double quotation closed. Now, I wanted wherever you find space, you would substitute that with Cologne, so I do not have any specific instance in mind. And hence the fourth option which is optional, not compulsory. I'm not using that as of now.

Let me close the bracket and see the results. All right, so wherever Spain's found it, substituted that with a colon. Now if I had in mind that I must repeat or substitute only the first instance of space, then, in which case, let me go to the last part of the formula, put a comma, and let me activate this fourth parameter which says instance number. So I say let's say two second instance, into. So it put color in the second instance, if I put one into, so it puts color only in the first instance. So depending on your choice, you can make sure you substitute all the spaces first, second, or third, that's substitute.

So quick round of theory. It is generally useful when you are replacing a specific instances of specific characters without knowing the specific location where it might exist in your original data. So that's substitute. Now how does it differentiate from replace formula? Let me scroll down to the part where I can talk about replays formula. It is useful for replay Using a specific portion of a string, where the location must be known, the location of the character which you want to replace must be known.

What do I mean by that, for example, in this case, the first location of space is in the fifth position. For the next name again, it's fifth, but for the next one, Jackson, so this consists of seven character, and the first instance of space is present in the eighth position. Now you must be aware of that. Now of course, it's very difficult for me to remember the stances of space for all different characters. So I'll be combining the search formula to first find the first instance of space. If it is found, then take that numeric position and use that in the Replace formula.

Let me first do it the manual way. That is replace that key. See the four parameters that comes up old text start num num characters, new text, how does It Work let me show you all text its original text on which I want to work. Now starting number, the replacement must start from fifth position. So 12345 start num should be five and you must be pleased one character from there. So number of characters to be replaced one that means that space will be taken off.

And in place of that space, I want to insert this new character that has semi colon sorry colon, double quotation, closing the brackets. Had I put in zero net position that is number of characters zero do not replace any characters, just that you add one character in which case you will find the tuni colon and then the space comes up. Now you must know the position number where you want to start to replace so hence, I will be using search why search because if I copy this formula down, you will notice the Jackson is not coming out as the correct answer, not even hiring. Why? Because the fifth position for Jackson is this one where it's just beginning with the s. So now let's see how to use the search formula. Let me delete this and rewrite it the place the old text is the easiest one to choose.

Now starting from position that I must find using the search formula, so let me write a search formula and the search formula I am posting in place of start num. Now what is the thing that I want to search for? I want to search for one space, that's fine text within which text within this text so this search formula will only give you the numeric value let's say five or six where the space is beginning. Comma. So if I just recapitulate the last two parts, old text is the original self In which I want to work around, the search helps me find out from where I must begin the replacement. I do not want to remove the space.

If I do not want to remove the space, I must put in a zero number of characters to replace none. But yes, I want to replace that space. So from the fifth position, replaced that one character. And in its position, give me the new text, which is called closing the brackets. Enter. Now let's see the results.

Wonderful. And if I want to decipher what's going inside the formula, a quick technique is you select the formula search only search and press f9 you will notice this turns out to be number. Let me press escape not enter. Escape. So that's the difference between substitute and replace. And amongst the to substitute comes out more handy because you don't have to worry about where the space might be.

Exist. Whereas in replays you have to find the position where the space does exist fifth position or sixth position. So those are two text formulas. We'll talk about more techniques in the videos to come. Thank you