How to delete text before or after a certain character in Excel

In the recent couple of articles, we've looked at different ways to remove characters from strings in Excel. Today, we'll investigate one more use case - how to delete everything before or after a specific character.

Delete text before, after or between 2 characters with Find & Replace

For data manipulations in multiple cells, Find and Replace is the right tool. To remove part of a string preceding or following a specific character, these are the steps to perform:

  1. Select all the cells where you want to delete text.
  2. Press Ctrl + H to open the Find and Replace dialog.
  3. In the Find what box, enter one of the following combinations:
    • To eliminate text before a given character, type the character preceded by an asterisk (*char).
    • To remove text after a certain character, type the character followed by an asterisk (char*).
    • To delete a substring between two characters, type an asterisk surrounded by 2 characters (char*char).
  4. Leave the Replace with box empty.
  5. Click Replace all.

For example, to remove everything after a comma including the comma itself, put a comma and an asterisk sign (,*) in the Find what box, and you'll get the following result:
Remove text after a specific character.

To delete a substring before a comma, type an asterisk, a comma, and a space (*, ) in the Find what box.

Please notice that we are replacing not just a comma but a comma and a space to prevent leading spaces in the results. If your data is separated by commas without spaces, then use an asterisk followed by a comma (*,).
Remove text before a specific character.

To delete text between two commas, use an asterisk surrounded by commas (,*,).
Remove text between two characters

Tip. If you'd rather have the names and phone numbers separated by a comma, then type a comma (,) in the Replace with field.

Remove part of text using Flash Fill

In modern versions of Excel (2013 and later), there is one more easy way to eradicate text that precedes or follows a specific character - the Flash Fill feature. Here's how it works:

  1. In a cell next to the first cell with your data, type the expected result and press Enter.
  2. Start typing an appropriate value in the next cell. Once Excel feels the pattern in the values you are entering, it will display a preview for the remaining cells following the same pattern.
  3. Hit the Enter key to accept the suggestion.

Done!
Remove part of text with Flash Fill

Remove text using formulas

In Microsoft Excel, many data manipulations performed by using inbuilt features can also be accomplished with a formula. Unlike the previous methods, formulas do not make any changes to the original data and give you more control over the results.

How to remove everything after a specific character

To delete text after a particular character, the generic formula is:

LEFT(cell, SEARCH("char", cell) -1)

Here, we use the SEARCH function to get the position of the character and pass it to the LEFT function, so it extracts the corresponding number of characters from the start of the string. One character is subtracted from the number returned by SEARCH to exclude the delimiter from the results.

For example, to remove part of a string after a comma, you enter the below formula in B2 and drag it down through B7:

=LEFT(A2, SEARCH(",", A2) -1)
Formula to remove everything after a specific character

How to remove everything before a specific character

To delete part of a text string before a certain character, the generic formula is:

RIGHT(cell, LEN(cell) - SEARCH("char", cell))

Here, we again calculate the position of the target character with the help of SEARCH, subtract it from the total string length returned by LEN, and pass the difference to the RIGHT function, so it pulls that many characters from the end of the string.

For example, to remove text before a comma, the formula is:

=RIGHT(A2, LEN(A2) - SEARCH(",", A2))

In our case, the comma is followed by a space character. To avoid leading spaces in the results, we wrap the core formula in the TRIM function:

=TRIM(RIGHT(A2, LEN(A2) - SEARCH(",", A2)))
Formula to remove everything before a certain character

Notes:

  • Both of the above examples assume that there is only one instance of the delimiter in the original string. If there are multiple occurrences, text will be removed before/after the first instance.
  • The SEARCH function is not case-sensitive, meaning it makes no difference between lowercase and uppercase characters. If your specific character is a letter and you want to distinguish the letter case, then use the case-sensitive FIND function instead of SEARCH.

How to delete text after Nth occurrence of a character

In situation when a source string contains multiple instances of the delimiter, you may have a need to remove text after a specific instance. For this, use the following formula:

LEFT(cell, FIND("#", SUBSTITUTE(cell, "char", "#", n)) -1)

Where n is the character's occurrence after which to remove text.

The internal logic of this formula requires using some character that is not present anywhere in the source data, a hash symbol (#) in our case. If this character occurs in your data set, then use something else instead of "#".

For example, to remove everything after the 2nd comma in A2 (and the comma itself), the formula is:

=LEFT(A2, FIND("#", SUBSTITUTE(A2, ",", "#", 2)) -1)
Deleting text after Nth occurrence of a character

How this formula works:

The key part of the formula is the FIND function that calculates the position of the nth delimiter (comma in our case). Here's how:

We replace the 2nd comma in A2 with a hash symbol (or any other character that does not exist in your data) with the help of SUBSTITUTE:

SUBSTITUTE(A2, ",", "#", 2)

The resulting string goes to the 2nd argument of FIND, so it finds the position of "#" in that string:

FIND("#", "Emma, Design# (102) 123-4568")

FIND tells us that "#" is the 13th character in the string. To know the number of characters preceding it, just subtract 1, and you'll get 12 as the result:

FIND("#", SUBSTITUTE(A2, ",", "#", 2)) - 1

This number goes directly to the num_chars argument of LEFT asking it to pull the first 12 characters from A2:

=LEFT(A2, 12)

That's it!

How to delete text before Nth occurrence of a character

The generic formula to remove a substring before a certain character is:

RIGHT(SUBSTITUTE(cell, "char", "#", n), LEN(cell) - FIND("#", SUBSTITUTE(cell, "char", "#", n)) -1)

For example, to strip off text before the 2nd comma in A2, the formula is:

=RIGHT(SUBSTITUTE(A2, ",", "#", 2), LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", 2)) -1)

To eliminate a leading space, we again use the TRIM function as a wrapper:

=TRIM(RIGHT(SUBSTITUTE(A2, ",", "#", 2), LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", 2))))
Deleting text before Nth occurrence of a character

How this formula works:

In summary, we find out how many characters are after the nth delimiter and extract a substring of the corresponding length from right. Below is the formula break down:

First, we replace the 2nd comma in A2 with a hash symbol:

SUBSTITUTE(A2, ",", "#", 2)

The resulting string goes to the text argument of RIGHT:

RIGHT("Emma, Design# (102) 123-4568", …

Next, we need to define how many characters to extract from the end of the string. For this, we find the position of the hash symbol in the above string (which is 13):

FIND("#", SUBSTITUTE(A2, ",", "#", 2))

And subtract it from the total string length (which equals to 28):

LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", 2))

The difference (15) goes to the second argument of RIGHT instructing it to pull the last 15 characters from the string in the first argument:

RIGHT("Emma, Design# (102) 123-4568", 15)

The output is a substring " (102) 123-4568", which is very close to the desired outcome, except a leading space. So, we use the TRIM function to get rid of it.

How to remove text after the last occurrence of a character

In case your values are separated with a variable number of delimiters, you may want to remove everything after the last instance of that delimiter. This can be done with the following formula:

LEFT(cell, FIND("#", SUBSTITUTE(cell, "char", "#", LEN(cell) - LEN(SUBSTITUTE(cell, "char ", "")))) -1)

Suppose column A contains various information about employees, but the value after the last comma is always a telephone number. Your goal is to remove phone numbers and keep all other details.

To achieve the goal, you can remove text after the last comma in A2 with this formula:

=LEFT(A2, FIND("#", SUBSTITUTE(A2, ",", "#", LEN(A2) - LEN(SUBSTITUTE(A2, ",","")))) -1)

Copy the formula down the column, and you'll get this result:
Removing text after the last occurrence of a character

How this formula works:

The gist of the formula is that we determine the position of the last delimiter (comma) in the string and pull a substring from left up to the delimiter. Getting the delimiter's position is the trickiest part, and here's how we handle it:

First, we find out how many commas there are in the original string. For this, we replace each comma with nothing ("") and serve the resulting string to the LEN function:

LEN(SUBSTITUTE(A2, ",",""))

For A2, the result is 35, which is the number of characters in A2 without commas.

Subtract the above number from the total string length (38 characters):

LEN(A2) - LEN(SUBSTITUTE(A2, ",",""))

… and you will get 3, which is the total number of commas in A2 (and also the ordinal number of the last comma).

Next, you use the already familiar combination of the FIND and SUBSTITUTE functions to get the position of the last comma in the string. The instance number (3rd comma in our case) is supplied by the above-mentioned LEN SUBSTITUTE formula:

FIND("#", SUBSTITUTE(A2, ",", "#", 3))

It appears that the 3rd comma is the 23rd character in A2, meaning we need to extract 22 characters preceding it. So, we put the above formula minus 1 in the num_chars argument of LEFT:

LEFT(A2, 23-1)

How to remove text before the last occurrence of a character

To delete everything before the last instance of a specific character, the generic formula is:

RIGHT(cell, LEN(cell) - FIND("#", SUBSTITUTE(cell, "char", "#", LEN(cell) - LEN(SUBSTITUTE(cell, "char", "")))))

In our sample table, to eradicate text before the last comma, the formula takes this form:

=RIGHT(A2, LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", LEN(A2) - LEN(SUBSTITUTE(A2, ",","")))))

As a finishing touch, we nest it into the TRIM function to eliminate leading spaces:

=TRIM(RIGHT(A2, LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", LEN(A2) - LEN(SUBSTITUTE(A2, ",",""))))))
Removing text before the last occurrence of a character

How this formula works:

In summary, we get the position of the last comma as explained in the previous example and subtract it from the total length of the string:

LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", LEN(A2) - LEN(SUBSTITUTE(A2, ",",""))))

As the result, we get the number of characters after the last comma and pass it to the RIGHT function, so it brings that many characters from the end of the string.

Custom function to remove text on either side of a character

As you have seen in the above examples, you can resolve almost any use case by using Excel's native functions in different combinations. The problem is that you need to remember of handful of tricky formulas. Hmm, what if we write our own function to cover all the scenarios? Sounds like a good idea. So, add the following VBA code to your workbook (the detailed steps to insert VBA in Excel are here):

Function RemoveText(str As String, delimiter As String, occurrence As Integer, is_after As Boolean) Dim delimiter_num, start_num, delimiter_len As Integer Dim str_result As String delimiter_num = 0 start_num = 1 str_result = "" delimiter_len = Len(delimiter) For i = 1 To occurrence delimiter_num = InStr(start_num, str, delimiter, vbTextCompare) If 0 < delimiter_num Then start_num = delimiter_num + delimiter_len End If Next i If 0 < delimiter_num Then If True = is_after Then str_result = Mid(str, 1, start_num - delimiter_len - 1) Else str_result = Mid(str, start_num) End If End If RemoveText = str_result End Function

Our function is named RemoveText and it has the following syntax:

RemoveText(string, delimiter, occurrence, is_after)

Where:

String - is the original text string. Can be represented by a cell reference.

Delimiter - the character before/after which to remove text.

Occurrence - the instance of the delimiter.

Is_after - a Boolean value that indicates on which side of the delimiter to remove text. Can be a single character or a sequence of characters.

  • TRUE - delete everything after the delimiter (including the delimiter itself).
  • FALSE - delete everything before the delimiter (including the delimiter itself).

Once the function's code is inserted in your workbook, you can remove substrings from cells using compact and elegant formulas.

For example, to erase everything after the 1st comma in A2, the formula in B2 is:

=RemoveText(A3, ", ", 1, TRUE)

To delete everything before the 1st comma in A2, the formula in C2 is:

=RemoveText(A3, ", ", 1, FALSE)

Since our custom function accepts a string for the delimiter, we put a comma and a space (", ") in the 2nd argument to spare the trouble of trimming leading spaces afterwards.
Custom function to remove text before or after a specific character

Our custom function works beautifully, doesn't it? But if you think it's the comprehensive solution, you haven't seen the next example yet :)

Delete everything before, after or between characters

To get even more options for removing individual characters or text from multiple cells, by match or position, add our Ultimate Suite to your Excel toolbox.

Here, we'll take a closer look at the Remove by Position feature located on the Ablebits Data tab > Text group > Remove.
Remove text by position

Below, we will cover the two most common scenarios.

Remove everything before or after certain text

Suppose all your source strings contain some common word or text and you wish to delete everything before or after that text. To have it done, select your source data, run the Remove by Position tool, and configure it like shown below:

  1. Select the All characters before text or All characters after text option and type the key text (or character) in the box next to it.
  2. Depending on whether uppercase and lowercase letters should be treated as different or the same characters, check or uncheck the Case-sensitive box.
  3. Hit Remove.

In this example, we are removing all characters preceding the word "error" in cells A2:A8:
Remove everything before certain text

And get exactly the result we are looking for:
All characters preceding certain text are removed.

Remove text between two characters

In situation when irrelevant information is between 2 specific characters, here's how you can quickly delete it:

  1. Choose Remove all substrings and type two characters in the below boxes.
  2. If the "between" characters should be removed too, check the Including delimiters box.
  3. Click Remove.
    Remove text between two characters

As an example, we delete everything between two tilde characters (~), and get the perfectly cleaned strings as the result:
Everything between two characters is deleted.

To try other useful features included with this multi-functional tool, I encourage you to download an evaluation version at the end of this post. Thank you for reading and hope to see you on our blog next week!

Available downloads

Remove first or last characters - examples (.xlsm file)
Ultimate Suite - trial version (.exe file)

Table of contents

45 comments

  1. I learned few things here. Thank you for sharing this knowledge.

  2. INV9999999 / 123456 This is cell. I need to get the invoice # in one cell and the 123456 in another cell. Can you help me?

  3. This was an amazing help! Thank you!

  4. Hi,
    Can you please help me on how to trim text in the cell?
    For example, I want to trim 01-0241-1 and rebuild kit from text (01-0241-1 -- rebuild kit).

    Thanks a lot

    • Hi! If you want to trim the text in a cell, use the recommendations in the article above. For example, find the position of the double dash "--" and extract all the characters up to that position.

      =LEFT(A1,SEARCH("--",A1)-1)

      If this does not help, explain the problem in detail.

      • Hi,
        I tried =LEFT(A2; SEARCH("--"; A2) -1) and the result was 01-0241-1. It was right.
        I also tried =TRIM(RIGHT(A2; LEN(A2) - SEARCH("--"; A2))) but the result was - rebuild kit. I was trying to show only "rebuild kit" in the result.
        Can you please help?

        Thanks,

        • Hi! If my understanding of your question is correct, if you want to split a cell at the position of the double dash "-" into two text strings, use these formulas:

          =MID(A1,SEARCH("--",A1)+2,50)
          =LEFT(A1, SEARCH("--", A1) -1)

          For more information, please visit: Excel MID function – extract text from the middle of a string.
          You can also use the new TEXTSPLIT function to split text into cells:

          =TEXTSPLIT(A1,"--")

          I hope it’ll be helpful.

          • Hi,
            Tried =MID(A1,SEARCH("--",A1)+2,50) and it worked.
            Thanks a lot for your help on this.
            Much appreciated.

  5. Hello! I am trying to remove text before a character, but the data has multiple characters.

    Ex: My data looks like

    123A
    123B
    123C

    I would like to remove the text before A, B, C.

    I am currently using:

    =LEFT(cell, SEARCH("A", cell) -1)
    but this only works for one character at a time.

    • Hi! Your formula does not remove the text before the character, it extracts it. To extract the text before three different characters, you can combine 3 formulas using the IFERROR function.

      =IFERROR(LEFT(A1,SEARCH("A",A1)-1), IFERROR(LEFT(A1,SEARCH("B",A1)-1), LEFT(A1,SEARCH("C",A1)-1)))

      I recommend reading this guide: Excel IFERROR function with formula examples.
      I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.

  6. Hello. I am trying to remove text from cells before Desc:, and move to a new column. Is there a way to do this? Thank you in advance!

    Part: 0514086 Desc: TIRE, 305/70R22.5, HA3, URBAN, CONTINENTAL Part Org : FLEET
    Part: 5310990 Desc: COIL, IGNITION, CUMMINS SUPERSEDED BY 5626388 Part Org : FLEET
    Part: 102-915 Desc: COMPRESSOR, A/C, S616LS Part Org : FLEET

    • Hi! Please re-check the article above since it covers your case. If I understood the question correctly, here is an example formula with the REPLACE function:

      =REPLACE(A1,1,SEARCH("Desc:",A1)-1,"")

      Hope this is what you need.

      • That worked! I think I confused myself when reading the article. Thank you so much!

  7. Hi please help me with formula

    12: DO[1:CYCLE COMP]=OFF ;

    i want to remove all spaces and text in between : to ] symbol and SEMICOLON also want to remove from last.

    DO[1]=OFF

    i need result like above

    please suggest any formula.

    • Hi! Pay attention to the following paragraph of the article above: Remove text between two characters. Use Remove By Position tool as described in article above. This will solve your problem in a couple of clicks. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.
      To extract text before the ":" character, use the TEXTBEFORE function. To extract text after "]", use the TEXTAFTER function. Try this formula:

      =TEXTBEFORE(A1,":")&"]"&TEXTAFTER(A1,"]")

  8. Hi, I need your help! I need to remove part of the text in multiple cells in excel/google sheet.
    Exp: Employee: Jose Pinesa. I need to romove Employee:

    How can I do that to save me time?

    Thank you

  9. how to remove text between , and :
    like the actuall text is ,part number:23232,
    but i need output like this ,23232,

    • Hi! Pay attention to the following paragraphs of the article above: Remove everything after a character and Delete everything before a character.

      =LEFT(RIGHT(A2, LEN(A2) - SEARCH(":", A2)), SEARCH(",", RIGHT(A2, LEN(A2) - SEARCH(":", A2))) -1)

  10. "WORK COMPLETED 
     
    BLADDER REPAIRED  "

    how do i make above characters need to be like " WORK COMPLETED BLADDER REPAIRED"

    please assist me

  11. How may I eliminate or removed the text from the time? Example: 8:30 am Work from Home...

    Thank you.

  12. I have to separate the multiple entries in column in format "Event Verbatim
    [Preferred Term]
    Ser/UL/Causal"
    in 3 parallel rows i.e. row 1- Event Verbatim, row 2- [Preferred Term] and row 3-Ser/UL/Causal"

    An example of above format is illustrated below:
    "This dose was not administered to the patient [INTERCEPTED PRODUCT PREPARATION ERROR]
    N / Y / N
    User handling error [WRONG TECHNIQUE IN PRODUCT USAGE PROCESS]
    N / Y / N
    When the nurse was mixing the 2 syringes, they came apart and the drug was lost [SYRINGE ISSUE]
    N / Y / N
    When the nurse was mixing the 2 syringes, they came apart and the drug was lost [DEVICE LEAKAGE]
    N / Y / N
    "

  13. Hello dear Team,
    I need your help, i have the following cells and I need the result is status in front of them.
    Thanks a lot for this:-
    1.8041-GTN to DC-44 Firdous Market the result should be GTN
    2.8061-ARZ (2/0/7) to C-3/4 Bhobhtian Chowk the result should be ARZ.
    3.8059-TNB (11/0/2) to C-16 Eden Value Homes the result should be TNB

  14. Hello,

    I really need help.

    I have to extract the last name from the middle

    The cells Look like this:

    1100 Lastname, Firstname

    So I need to remove the 1100 and the comma and the firstname at the same time from like 20 names

    Thanks a lot.

  15. Hello,

    I am trying to extract numbers from cells that have text followed by numbers in a separate line. For example:

    Early Spring Application
    438
    -
    Mosquito #5
    27
    -
    Wilt Proof
    2

    Each of these pairings is one cell. I would like to write a formula to grab the numbers in the bottom lines. I have hit a wall because the numbers vary between 1 and 3 digits, and the application names vary in length. Is this doable? Any help is extremely appreciated!

      • This worked perfectly! Thank-you! I was not going to get there myself. Been a little while since I used excel on a daily basis...

  16. Great :)

  17. Hi, I want to remove the line break before all lowercase characters. Please let me know if there have any solutions

    example -
    Impactful
    speeches
    • Staying

    Output-
    Impactful speeches
    • Staying

    Regards & Thanks,

    • Hello!
      To remove line break before specific character try this formula

      =IF(AND(CODE(MID(B2,SEARCH(CHAR(10),B2)+1,1)) > 96, CODE(MID(B2,SEARCH(CHAR(10),B2)+1,1)) < 123), REPLACE(B2,SEARCH(CHAR(10),B2),1," "),B2)

      I hope my advice will help you solve your task.

  18. Hello Sir,

    I required VB function code for only removal XXXX.XXXMTQ XXX.XXXFTQ.

    Example - as per showing

    ASLS2O11252 1 BULK 20150.000KGS 25.000CBM
    SN# 15577 44423.146MTQ 882.867FTQ

    Need output - without removal SN Number and MTQ/FTQ number will be random number.

    ASLS2O11252 1 BULK 20950.000KGS 25.000CBM
    SN# 15577

  19. Hello team,
    Can you help me to change PROD-001 to Product 1, I have been using multiple separate formula so it's kind of ugly.
    I would be appreciated a lot. Thank you

  20. Thanks a lot Sir,
    I applied this formula it is working :-=LEFT(A1,MIN(IFERROR(FIND({1;2;3;4;5;6;7;8;9;0},A1,1),99999))-1)&" "&MID(A1,MIN(IFERROR(FIND({1;2;3;4;5;6;7;8;9;0},A1,1),99999)),50)

    but only one string no changed i.e.65Hanjarwal
    It is still 65Hanjarwal
    can you help me I need like this result : 65 Hanjarwal

  21. Hello dear Team,
    I need your help, that I want make spaces between the text and number below
    strings i tried myself but not get success.

    text string
    65Hanjarwal
    C2160feetRoad
    C19JaffriaColony
    G14sunderIndustrialstate
    CANAL BANK2
    C18AshyanaHScty
    222-LT-LTS-M15FZR
    C41OPP240B
    C2 84981C12
    C13OPP3402C2

    Thanks a lot

    • Hi!
      To insert a space before the first digit you can use this formula:

      =LEFT(A1,MIN(IFERROR(FIND({1;2;3;4;5;6;7;8;9;0},A1,1),99999))-1)&" "&MID(A1,MIN(IFERROR(FIND({1;2;3;4;5;6;7;8;9;0},A1,1),99999)),50)

  22. This saved me so much time and energy. Super easy, way more efficient than manually updating 10690 columns of data. Thank you!

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)