Comments on: How to remove characters/text from string in Excel

In this tutorial, we will look at the most common cases of removing characters in Excel. Want to delete specific text from multiple cells? Or maybe strip the first or last character in a string? Or perhaps remove only a specific occurrence of a given character? Whatever your task is, you will find more than one solution for it! Continue reading →

Comments page 2. Total comments: 96

  1. Dear Concern,

    I have a list of data as below around 9000 row. From each of row I want to remove all text after "M" (3000M). How can I do this please advise. Thanks.

    Eco Poly; 100% Premium Poly Poly Corespun; Tex-60; Tkt-50; 3000M (44049)

    Best regrads,
    Mahbub Ar Rashid
    Dhaka, Bangladesh

  2. A2 = Party's Name
    B2 = Address with pin

    A2 - Aaditi Krushi Seva Kendra-Lingali
    B2 - Aaditi Krushi Seva Kendra-Lingali & his full address

    I have data in 2000 row, I want in C2 only address without party's Name.
    Can you help me

    1. Hi!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =SUBSTITUTE(B2,A2,"")

      You can learn more about SUBSTITUTE function in Excel in this article on our blog.

  3. A2 B2
    Party's Name Address with pin
    Aaditi Krushi Seva Kendra-Lingali "Aaditi Krushi Seva Kendra-Lingali, (full address)
    Aaditya Krushi Seva Kendra-Nitrud Aaditya Krushi Seva Kendra-Nitrud (full address)
    Aae Pedkae K.S.Kendra, Ashte Aae Pedkae K.S.Kendra, Ashte (full address)

    have data in Large number of row, I want in C2 only address without party's Name.
    Can you help me

  4. How can I clean this:
    102095
    105679-02
    100794_D
    CBT106145

    Result:
    102095-00
    105679-02
    100794-00
    106145-00

    I want to get rid of the letter and the underscore only.

  5. Is there a way to remove three words from an excel spreadsheet. the three words needing to be removed from each cell are all of different lengths but are all separated by a space

  6. Hi Alexander,

    I have name ABC Travels and Tours in A1 and in A2 i have used following formula =TRIM(LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",2)&"^"))) to arrive at the following result: ABC Travels.

    Now instead of Travels just require Travel so what will be the formula that i can use.

    Regards/Irfan

      1. Dear Alexander,

        Thank you for your reply, I tried the formula you suggested, However it gives the result with one character less for the words in second place.
        Since I have data in thousand of rows and i only want the result with one less character of particular word i.e Travels and formula should give me the result as Travel.

        Irfan

          1. Hello!

            Yes Correct.

            1. Hello Alexander,

              I have figured out the following formula.

              =TRIM(LEFT(A1,FIND("^",SUBSTITUTE(SUBSTITUTE(A1," ","^",2),"TRAVELS"," ")&"^")))

              will above formula be consistent and work smoothly?

              Regards/Irfan

              1. =TRIM(LEFT(A1,FIND("^",SUBSTITUTE(SUBSTITUTE(A1," ","^",2),"TRAVELS"," ")&"^")))

                Note: After Travels in inverted commas i have kept 5 spaces

  7. Hello :)

    I need to remove the last digits in a string of text eg “Thomas Andrew jeffery 5555” the length of number at the end may vary

  8. Hello, I want to remove all the characters to the right of the date sequence.

    The date sequence is written as Year,Month , Day.

    All I want after its done is for the data in each column to remain.

    Example of unmodified cells:

    20110904_Acts1_1-3.mp3
    20110911_Acts1_4-8.mp3
    20110918_Acts2_1-4.mp3

    How I want it to look afterward:

    20110904
    20110911
    20110918

    What im ultimately trying to do is have the Date in one Column and what comes after the date in another column. However the person who started it this was listen the data and the title in the same string.

    Ultimately it would look like this:

    COLUMN 1 COLUMN2
    20110904 Acts1_1-3.mp3
    20110911 Acts1_4-8.mp3
    20110918 Acts2_1-4.mp3

    1. Hello!
      You can manually split a string with the Text to Columns feature.
      You can split text using formulas, as described in this article.

      =LEFT(A2, SEARCH("_",A2)-1)

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

      You can also use the new Excel functions TEXTBEFORE and TEXTAFTER to get the string before and after the delimiter.

      =TEXTBEFORE(A2,"_",1)
      =TEXTAFTER(A2,"_",1)

      This should solve your task.

  9. Hi,

    I have the following text on a cell: Matthew Stafford\StafMa00
    and I need to only have Last Name, First Name (Stafford, Matthew)

    Thanks so much!

  10. Hi,

    I need to adjust a sequence of numbers for an efficient pivot sheet, right now I manually remove the additional characters and it’s extremely time consuming. An example is below:

    Ping: James Carpenter 38754689 15
    Ping: James Carpenter 38763532 15
    Ping: Janet Jackson 47627645 15
    Ping: Ming Lee 47657374 15
    Ping: Seymour Leften 36875267 15

    I just need “Ping: (first/last name)” or “Ping: James Carpenter…

    What formula could I use to simplify each cell in order to save time on my pivot sheets?

    Thanks!
    VR

    1. Hello!
      If your data has a common pattern, then extract all characters up to the third space. Replace the third space with the SUBSTITUTE function. Find its position using the SEARCH function.
      Please try the following formula:

      =LEFT(A1,SEARCH("#",SUBSTITUTE(A1," ","#",3),3))

  11. Hi, hope you can help me.
    I have a sheet that contains cells with data I do not want. Such as:
    "Save"
    "View profile"
    "Add Phil Nock to selection"
    etc...
    They all have a pattern, in the sense that the cells I do not want all have a pattern throughout the sheet, either containing a whole word in a cell like "add" or with text that follows the common word "add Phil Nock..."

    I have a list of common words that are found after i have sorted my list to satisfaction, but have no clue how to "Find" a word in a cell, and "remove" who whole cell if that word is found, whether it is in whole or in part "add" or "add Phil..."

  12. i have case about IP
    118.98.100.117
    118.98.101.1
    118.98.101.108
    118.98.101.12
    118.98.101.123

    i need to remove the last character after "." and add with "0/24"
    the result should be 118.98.101.0/24

  13. Thank you so much... you just saved me many hours... :-)

  14. Sir i want to remove text into columns from every cells but i can't find any solution .It is very difficult for me .
    following text is to be extract:
    Saadan Hospital (Johar Town) Rs. 1,500Available todayRs. 1,500 Iqra Medical Complex (Ext.) (Johar Town) Rs. 2,000Available tomorrowRs. 2,000 Zainab Medical Centre (Wapda Town) (Wapda Town) Rs. 1,500Available todayRs. 1,500 Online Video Consultation (Online) Rs. 1,200Available todayRs. 1,200 Tahir Hospital (Raiwind Road) Rs. 1,500 Schedule: N/ARs. 1,500

    Sir i want to extract in following type
    Saadan Hospital (Johar Town), Iqra Medical Complex (Ext.) (Johar Town), Zainab Medical Centre (Wapda Town), Tahir Hospital (Raiwind Road)

    Thanks M Mushahid

  15. I have series of strings
    UNUNK1986
    UNJUN1986
    01JUN1986
    in the same column

    I want to remove "UNUNK" from UNUNK1986 and "UN" from UNJUN1986 and need to apply text join in next column.

    Thank you
    Varun

  16. Hi I have a string of reviews mined from a review site like this:

    Trish Mar 30 2022 9:09 pm
    This series moved me to tears quite a few times. BRILLIANT acting by all actors and actresses involved. Thank You for your hard work. CHEERS!

    How do I remove the name and date/time for all 1518 rows?

  17. Vehicle number
    1st case

    GJ5AB9998 need to add 0 before 5
    Need to see like GJ05AB9998

    2nd case

    GJ05AB998
    Need add 0 before 998

  18. Hi,

    I have the following string in a cell:
    ME.SA.02.14 (MSG)

    I need a formula that would help me get the following string ME.SA.02 (MSG)

  19. Hello,

    I have a string in a cell 0108997237110515211116342164401725022810TMC22009

    I want to extract 21111634216440 from it and paste that at the back after 9

    is this possible ?

  20. Hi guys,

    I was wondering if the CTRL+H option can be done automatically.

    I mean, when I scan an article on Excel there is always a #00 after the code (ex: BDC#00). Is there a VBA to remove automatically the #00 on range B8:B20 after to press Enter and keep only "BDC"?

    Thank you a lot for your help.

    Patrick.

      1. Hi Alex,

        thanks for your reply. However and due to another VBA, cells cannot have any functions. It has to be only with a VBA code such as

        Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

        Do you have any idea how to build this VBA`?

        Thanks you so much.

        Pat.

  21. thank you!

  22. I'm trying to combine a RIGHT/LEN to remove the first X# of characters, which is fine, but I'm trying to leverage a vlookup to get the value to remove. I can nest the VLOOKUP in the RIGHT formula, but can't seem to figure out how to add the LEN after that lookup and reference the value the VLOOKUP is returning. Any suggestions without doing 2 columns of lookups?

    1. Hello!
      So that I can help you, write an example of the source data and explain what result you want to get.

    2. Logically, what I want it something like this =RIGHT(VLOOKUP.....),LEN(*result of the vlookup),-10

  23. This is very helpful. God bless you ?

  24. Well done!!

  25. Is it possible to remove strings of text from other cells if they repeat in a previous cell? I am trying to do this with any random partial duplicates. I am trying keep the first occurrence of a string but delete that string from other cells.

    For example, I want to delete any strings that repeat and are more than 2 characters long.

    abc
    def
    ghi
    jkl
    abdgj
    adetyk

    Since "ab" is in the first cell and also the 5th cell, is it possible to delete the "ab" in the 5th cell? So the first cell will still be "abc" but the 5th will change to "dgi".

    "de" also repeats in 2nd and 6th cell, "de" to only remain in the 2nd cell but be deleted in the 6th cell. 2nd cell will remain "def" but the 6th cell will change to "atyk".

    Is this possible? I have been looking it up for hours but can't seem to find a solution.

    Thank you very much.

    1. Hi!
      A formula may only modify the value of the cell into which it is written. Your task cannot be solved using ordinary Excel formulas. You need to use the VBA macro.

  26. This helped me solve CSV files where the broker Binance had spammed USDT and commas in every line!

  27. try this in MS Word's find and replace:

    Find: space^p
    Replace with: ^p
    Replace All , several times until trailing spaces (or space) at the end deleted

    ^p is a special character for replaces Enter (paragraph mark)
    for another purposes, look at More - Special

  28. I'm looking for a way to remove the last character (generally a space) at the end of a cell in WORD 2019.
    This space is generally included when I copy a spreadsheet from EXCEL to WORD.

    I can not find a way for the WORD 2019 to find the Cell Mark at the end of a cell in Word. If I can do this, I can concatenate the character + the cell mark and type ^H to call the find and replace window.

    Best Regards,
    Julio Borges
    Rio de Janeiro - Brasil

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 :)