How to replace string that matches specific condition in Excel

Sometimes you need to replace specific text that matches specific condition. Let’s take an example:

In the above excel sheet snap, There is some schools name of New delhi, and we need to replace the location name with New Delhi that placed at the end of the school name separated by a comma.

To do this task we will use three excel function together. i.e:

  1. To replace the text we use SUBSTITUTE function
  2. To take the string that needs to be replaced (in above example, the location name after the school name) we use MID function.
  3. To set the pointer from where we will start taking the text for MID function, (in the above example, we have to start after “,” where the location name starts) we use FIND function.

We will do this task in column B. So in cell B2 we will write the entire function.

Let’s go through the function.

For SUBSITUTE function we need three parameters

  • text: will be cell A2 on where we will perform replacing.
  • old_text: will be the location name after the hotel name. to get this text we will use MID function.
    For MID function we need three parameters i.e:

    • text: we be cell A2 on where we will perform this function.
    • start_num: will be from where we start taking. To get start_num we will use FIND function.
      For FIND function we need two parameters i.e:

      • find_text: will be “,” because after comma location name starts.
      • within_text: will be A2 where we will find.
      • So here find function will be
    • num_char: will be the number of character we will take. Here we will take to at the end of text from the comma. To get this we will subtract index of FIND from the total length of the text. to get the total length of text we will use LEN(A2) that gives us the total length of the text. So here in num_char we will write
    • So the complete MID function will be
  • new_tex: will be ” New Delhi”. Notice, we take a space before New Delhi because we start taking just after the comma in old_text so that space will remove that’s why we take space.

Here, the complete SUBSITUTE function will be

=SUBSTITUTE(A2,MID(A2,FIND(",",A2)+1,LEN(A2)-FIND(",",A2))," New Delhi")

We will put this function in Cell B2 and drag it till B11 so that function will be applied for all the school name.

So like this, We can use multiple combinations of function to achieve specific tasks.

Please comment me if you need any help in excel we will be happy to help you 😊


No Comments

Leave a reply

Post your comment
Enter your name
Your e-mail address

Story Page