DELETE substring of text between text

  • Hi,

    I have seen how there are ways to extract substrings of text from a string, but what I want to do is DELETE a substring of text that is between two spaces (i.e., the 3rd and the 5th). Therefore:

    aaa bbb ccc ddd eee fff ggg hhh iii

    should turn into:

    aaa bbb ccc fff ggg hhh iii


    I should add that the text I want to delete won't always be between the 3rd and 5th space; it may be between the 4th and 6th on one string, and then the 3rd and 5th on the next one. eeek?


    Dan B

  • Please upload a sample workbook showing a before and after scenario. Will the text always contain the same data that needs to be deleted? Be sure to provide specific criteria for the text to be deleted.

  • If you are using Excel 2010 or later here is a Power Query Solution

    I missed the part about 121 or 29. This solution ignores that part. What is the determination for 121 or 29 so that I can add that back into the solution?

  • Alan,

    Thanks for the quick response.

    I am running MS Excel 2016 (or 2013?--will confirm at the Office on Wednesday).

    1) The part about 121 or 29 is that they need to be in a particular format for our standards (Department of Transportation). Column A is a text string that should refer to the route after the county name (NAP-121-...) 121 or 29 are simply strings that would be replacing the 2nd set of characters all the way down. The only reason I was asking about them here is I thought there might be a slick way of doing a SUBSTITUTION with 121 or 29 all the way down.

    Nested SUBSTITUTE prompts actually work, but the problem is that I found they are text-string specific. As in I couldn't have the same SUBSTITUTE functions work all the way through the database-I could only use them for that particular record. If there was a way to refer to text strings between x and y spaces and replace them with z (empty space), then I'd be in heaven.

    2) As much as I love the script and look forward to trying it out, I am concerned about the size of the script--is there a way to condense it? Or where would I put it if I can't put it in a cell like I would for a formula?

    Thanks again,


  • Here is a link on how to use the Mcode provided. It needs to be inserted into Power Query.…r-query-paste-code-video/

    and I am still confused. Should we putting 029 or 121 in all cells? I can build it into the script but you need to provide the criteria.

  • Alan,

    To clarify:

    Naplabels.xlsx has the Before and After scenarios you were looking for. The ORIGINAL_TEXT has the initial text description, the UPGRADE_1 has an updated string with the Highway Route numbers (121 and 29) in the correct format that matches the format for other databases, and the FINAL field indicates the desired end result. This database will be used with another one with referencing County-Route-Postmile in the same format as what I want in the FINAL version.

    NAPdash is an illustration of where 121 and 29 are coming from (State Highway system) and where the FINAL end result will be going.

    I was able to use a Substitute function to replace the second "NAP" and the "YNTV" or the gap between the first NAP and the R, but I was only wondering if its possible to do that without having to adjust the Substitute function all the time (although I have a feeling I might be able to nest ...)?

    FYI I am running MS Excel 2016.

    Will respond soon re: the Script you sent.

    Thanks for all of your effort on this.


  • I still don't see any relationship in your uploaded spreadsheet that indicates whether 121 or 29 should be inserted. In order for this to happen, there has to be a logic scenario that triggers that. It cannot be just your knowledge if you want this to be automated.

  • Rtenumber.xlsx is a list of all the Route numbers for the State Highway System. Would it help if I was to put in a string of "-029-" in one of the fields?

    Naplabels0403 is the end result of inserting a Power Query. I don't know how I got away with it but here it is. (I had troubles with converting text to table in the Power Query Editor or compiler)?? The second sheet shows where I got to with the SUBSTITUTE function--I could only seem to replace the first part of the Text String the way I wanted to but I couldn't do it for more than one Instance because of how the second text strings varied so much. If there was a way to replace text strings with spaces no matter what their length is, that would do everything I want to.

    Maybe where I'm heading is to use nested SUBSTITUTE functions for X amount of characters between Y spaces in the Text String with end result being?

    But for now, in relation to your Power Query (script??):

    My question to you at this point is that even if you have a value that the IF THEN logic can go off of, how is it going to work when the text strings they need to replace are random and in so many formats?

  • Alan,

    For now, you can hold off; while I thank you for your efforts, I was able to find a solution from another source that used nested SUBSTITUTE functions.

    I will post what worked for me tomorrow.

    Thanks again and again.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!