How to remove characters/text from string in Excel

The article looks at how to quickly remove part of text from Excel cells using formulas and inbuilt features.

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!

How to remove specific character in Excel

If your goal is to eradicate a certain character from Excel cells, there are two easy ways to do it - the Find & Replace tool and a formula.

Remove character from multiple cells using Find and Replace

Bearing in mind that removing a character is nothing else than replacing it with nothing, you can leverage Excel's Find and Replace feature to accomplish the task.

  1. Select a range of cells where you want to remove a specific character.
  2. Press Ctrl + H to open the Find and Replace dialog.
  3. In the Find what box, type the character.
  4. Leave the Replace with box empty.
  5. Click Replace all.

As an example, here's how you can delete the # symbol from cells A2 through A6. Removing a given character from multiple cells

As the result, the hash symbol is removed from all of the selected cells at once, and a pop-up dialog informs you how many replacements have been made: The character is removed from all selected cells.

Tips and notes:

  • This method deletes characters directly in your source data. If the result is different from what you expected, press Ctrl + Z to undo the change and get your original data back.
  • If you are dealing with alphabetical characters where the letter case matters, click Options to expand the Find and Replace dialog, and then tick the Match case box to perform case-sensitive search.

Remove certain character from string using a formula

To eliminate a specific character from any position is a string, use this generic SUBSTITUTE formula:

SUBSTITUTE(string, char, "")

In our case, the formula takes this form:

=SUBSTITUTE(A2, "#", "")

Basically, what the formula does is that it processes the string in A2 and replaces each hash symbol (#) with an empty string ("").

Enter the above formula in B2, copy it down through B6, and you will get this result: Removing a certain character from a string

Please pay attention that SUBSTITUTE always returns a text string, even if the result contains only numbers like in cells B2 and B3 (notice the default left alignment typical for text values).

If you want the result to be a number, then wrap the above formula in the VALUE function like this:

=VALUE(SUBSTITUTE(A2, "#", ""))

Or you can perform some math operation that does not change the original value, say add 0 or multiply by 1:

=SUBSTITUTE(A2, "#", "")*1 Remove a character and return the result as a number.

Delete multiple characters at once

To remove multiple characters with one formula, simply nest SUBSTITUTE functions one into another.

For example, to get rid of a hash symbol (#), forward slash (/) and backslash (\), here's the formula to use:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "#",""), "/", ""), "\", "") Deleting multiple characters from a cell

Tips and notes:

  • The SUBSTITUTE function is case-sensitive, please keep that in mind when working with letters.
  • If you'd like to have the results as values independent on the original strings, use the Paste special - Values option to replace formulas with their values.
  • In situation when there are many different characters to remove, a custom LAMBDA-defined RemoveChars function is a lot more convenient to use.

How to remove certain text from Excel cell

The two methods we used for removing a single character can handle a sequence of characters equally well.

Delete text from multiple cells

To remove specific text from each cell in a selected range, press Ctrl + H to display the Find and Replace dialog, and then:

  • Enter the unwanted text in the Find what box.
  • Leave the Replace with box blank. Deleting specific text from multiple cells

Clicking the Replace All button will do all the replacements in one go: Part of text is removed from the selected cells.

Remove certain text from cell using a formula

To remove part of a text string, you again use the SUBSTITUTE function in its basic form:

SUBSTITUTE(cell, text, "")

For example, to delete the substring "mailto:" from cell A2, the formula is:

=SUBSTITUTE(A2, "mailto:", "")

This formula goes to B2, and then you drag it down across as many rows as needed: Removing specific text from a cell

How to remove Nth instance of a specific character

In situation when you want to delete a certain occurrence of a particular character, define the last optional argument of the SUBSTITUTE function. In the generic formula below, instance_num determines which instance of the specified character should be replaced with an empty string:

SUBSTITUTE(string, char, "", instance_num)

For example:

To eradicate the 1st slash in A2, your formula is:

=SUBSTITUTE(A2, "/", "", 1)

To strip the 2nd slash character, the formula is:

=SUBSTITUTE(A2, "/", "", 2) Formula to remove th instance of a specific character

How to remove first character

To remove the first character from the left side of a string, you can use one of the following formulas. Both do the same thing, but in different ways.

REPLACE(cell, 1, 1, "")

Translated into a human language, the formula says: in the specified cell, take 1 character (num_chars) from the 1st position (start_num), and replace it with an empty string ("").

RIGHT(cell, LEN(cell) - 1)

Here, we subtract 1 character from the total length of the string, which is calculated by the LEN function. The difference is passed to RIGHT for it to extract that number of characters from the end.

For instance, to remove the first character from A2, the formulas go as follows:

=REPLACE(A2, 1, 1, "")

=RIGHT(A2, LEN(A2) - 1)

The screenshot below shows the REPLACE formula. The RIGHT LEN formula will deliver exactly the same results. Removing the first character from a string

To delete any n characters from the beginning of a string, please see How to remove characters from left in Excel.

How to remove last character

To strip off the last character from the end of a string, the formula is:

LEFT(cell, LEN(cell) - 1)

The logic is similar to the RIGHT LEN formula from the previous example:

You subtract 1 from the total cell length and serve the difference to the LEFT function, so it can pull that many characters from the beginning of the string.

For instance, you can remove the last character from A2 using this formula:

=LEFT(A2, LEN(A2) - 1) Removing the last character from a cell

To delete any n characters from the end of a string, please see How to remove characters from right in Excel.

Remove text after a specific character

To delete everything after a given character, the generic formula is:

LEFT(string, SEARCH(char, string) -1)

The logic is quite simple: the SEARCH function calculates the position of the specified character and passes it over to the LEFT function, which brings the corresponding number of characters from the beginning. Not to output the delimiter itself, we subtract 1 from the SEARCH result.

For example, to remove text after a colon (:), the formula in B2 is:

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

For more formula examples, please see Delete text before or after a certain character.

How to remove spaces before and after text in Excel

In text processors such as Microsoft Word, a whitespace before text is sometimes added intentionally to create a balanced and elegant flow for the reader's eye. In spreadsheets programs, leading and trailing spaces may creep unnoticed and cause a lot of problems. Luckily, Microsoft Excel has a special function, named TRIM, to delete extra spaces.

The formula to remove excess spaces from cells is as simple as this:

=TRIM(A2)

Where A2 is your original text string.

As you can see in the image below, it deletes all spaces before text, after text and between words/substrings except for a single space character. Removing spaces before and after text

If this simple formula does not work for you, then most likely there are some non-breaking spaces or non-printing characters in your worksheet.

To get rid of them, convert non-breaking spaces into regular spaces with the help of SUBSTITUTE:

SUBSTITUTE(A2, CHAR(160), " ")

Where 160 is the code number of a non-breaking space character ( ).

Additionally, use the CLEAN function to eliminate non-printable characters:

CLEAN(SUBSTITUTE(A2, CHAR(160), " "))

Nest the above construction in the TRIM function, and you'll get a perfect formula to remove spaces before/after text as well as non-breaking spaces and non-printing characters:

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

For more information, please see How to remove spaces in Excel.

Remove characters in Excel with Flash Fill

In simple scenarios, Excel's Flash Fill can do you a favor and remove characters or part of text automatically based on the pattern that you provide.

Let's say you have a name and email address in one cell separated by a comma. You want to remove everything after the comma (including the comma itself). To have it done, carry out these steps:

  1. Insert a blank column to the right of your source data.
  2. In the first cell of a newly added column, type the value you want to keep (name in our case).
  3. Start typing the value in the next cell. As soon as Excel determines the pattern, it will show a preview of data to be filled in the below cells following the same pattern.
  4. Press the Enter key to accept the preview.

Done! Removing part of text with Flash Fillt

Note. If Excel is unable to recognize a pattern in your data, fill in a couple more cells manually to provide more examples. Also, make sure Flash Fill is enabled in your Excel. If it still does not work, then you'll have to resort to some other method.

Special tools to remove characters or text in Excel

This final section presents our own solutions for removing text from Excel cells. If you love finding simple ways to handle complex challenges, you'll enjoy the handy tools included with Ultimate Suite.

On the Ablebits Data tab, in the Text group, there are three options for removing characters from Excel cells:

  • Specific characters and substrings
  • Characters in a certain position
  • Duplicate characters
Special tools to remove characters and text in Excel

To delete a specific character or substring from selected cells, proceed in this way:

  1. Click Remove > Remove Characters.
  2. Select the option best suited to your needs.
  3. Check or uncheck the Case-sensitive box.
  4. Hit Remove.

Below are a few examples that cover the most typical scenarios.

Remove specific character

To remove a particular character(s) from multiple cells at once, select Remove custom characters.

As an example, we are deleting all occurrences of the uppercase letters A and B from the range A2:A4: Remove multiple characters from multiple cells at once

Delete a predefined character set

To remove a certain set of characters, select Remove character sets, and then choose one of the following options:

  • Non-printing characters­ - strips off any of the first 32 characters in the 7-bit ASCII set (code values 0 through 31) including a tab character, line break, and so on.
  • Text characters - removes text and keeps numbers.
  • Numeric characters - deletes numbers from alphanumeric strings.
  • Symbols & punctuation marks - removes special symbols and punctuation marks such as a period, question mark, exclamation point, comma, etc. Delete a predefined character set.

Remove part of text

To delete part of a string, choose the Remove a substring option.

For example, to extract usernames from Gmail addresses, we are removing the "@gmail.com" substring: Removing part of a string

That's how to remove text and characters from Excel cells. I thank you for reading and look forward to seeing you on our blog next week!

Available downloads

Remove characters in Excel - examples (.xlsm file)
Ultimate Suite - evaluation version (.exe file)

Table of contents

96 comments

  1. Please am trying to separate a text from a number like Paid -30/09/2024 14:24
    I want the paid, date and the time to be separate

  2. Thanks - I was trying to export thousands of records from Zoho, make a small amendment to each of them, and then reimport them according to the record ID. But the record ID values in the CSV all had the prefix of zcrm_, which the actual record number didn't.

    =substitute(cell number, "zcrm_", "") fixed it perfectly. Thanks!

  3. Hello,

    I am trying to separate or remove just the date on one cell tried textafter,textsplit still doesn't work

    example in one cell value includes date and time ==> "4/1/2024 05:00 AM" - I just want to remove the date or move it to another cell and remain just the time.

    Please help

    Thank you

    • Hello Navin!
      Unfortunately, I don't know what result you want: "05:00 AM", "05:00" or 05:00 as time.
      To extract the text between the second and third spaces "05:00", you can use these guidelines: Split string by comma, semicolon, slash, dash or other delimiter.
      The formula might look like this:

      =MID(A2, SEARCH(" ",A2) + 1, SEARCH(" ",A2,SEARCH(" ",A2)+2) - SEARCH(" ",A2) - 1)

      With this formula, you extract the time as text.
      With the TEXTBEFORE and TEXTAFTER functions, you can get the time as text:

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

      To get the time from the text, you can use the TIMEVALUE function:

      =TIMEVALUE(TEXTBEFORE(TEXTAFTER(A2," ",1)," "))
      =TIMEVALUE(MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+2)-SEARCH(" ",A2)-1))

      If the format of this text matches your system date and time settings, you can convert the text to date and time using a mathematical operation and then get the time value:

      =(MOD(--A2,1))

      Set the desired time format in the formula cell.

  4. Dear Team,

    Basic Data:

    1.Blocks Work
    2.Plasting Work
    3.Scaffolding Work
    4.Chiping Work
    5.Barbending Work
    6.Shuttering Work
    7.Cleaning Work
    8.Water Curing
    9.Welding Work & Cutting Work.

    Results:

    Blocks Work, Plasting Work, Scaffolding Work, Chiping Work, Barbending Work, Shuttering Work, Cleaning Work, Water Curing and Welding Work & Cutting Work.

    Is this possible ?

  5. Hello Sir,

    I have an inventory file with item description cell. I have to import this file to new pos. New pos requires description limit to 30 characters max. I do have items that has more than 30. I would like to cut the description of the items that has more than 30 characters to 30. Is there a way to remove few letters from the item description in the middle. I like to keep the first and last 15 characters and remove few fr0m the middle to make it total of 30.

    EXMP. Bombay sapphire London Dry Gin 750 ml. This has 37 total characters I want to reduce to 30 by removing 7 from the middle and leave first and last 15 as is.

    is there any formula for this? I have quite a big list. what is the best way to do this form multiple items.

    is there a function where you can use it to find more than 30 characters in a cell and reduce to 30 by keeping first and last 15.

    Thanks

      • Thanks for quick reply.
        Formula works perfectly for the cell that has more than 30 characters.
        But if the cell has less than 30 character it add more txt to make it 30 characters.

        EXMP: GATORADE 32 OZ it changes to "GATORADE 32 OZGATORADE 32 OZ"

        HOT MIX SNACKS > "HOT MIX SNACKSHOT MIX SNACKS"

        is there a function that this only work for more than 30 characters? I would just copy and paste this for all the cells and whichever cell has more than 30 characters, formula applies and if its 30 or less no change being done to that cell.

        Thanks again for your help.

        • Hi! To avoid wasting my time and yours, it would be nice if you described the problem in full. Also, your example result is 28 characters, not 30. You can paste the text from the cell twice and get the first 30 characters. For example:

          =IF(LEN(A1)>30,LEFT(A1,15)&RIGHT(A1,15),LEFT(A1&A1,30))

          • Thanks for Reply and let me explain in detail.

            here are the text in a 2 different cell. Let's say cell is B2 and other is B3 and so on.

            (B2) - Bombay Sapphire London Dry Gin 750 ML - (total 37 letters including spaces)

            (B3) - Water 1 LT - (10 characters)

            So I like to reduce the B2 cell characters from 37 to 30 and for this your first formula work perfect. The formula reduces the character from the middle and brings it down to 30.

            But If I use same formula for the cell B3, here is the outcome of applying the formula. (Water 1LTWater 1LT)

            Because I have more than 1000 cells with those kind of description where some cells would be more than 30 letters and some would under 30.

            I can not pick and apply formula individually because I have more than 1000 cells.

            I was asking that if there was a way to use the formula that would only reduce if the cell has more than 30 letters and does not make any changes to the cell that has 30 or less.

            So I would like to copy the formula to the all the cells and formula only works for the cell with more than 30 letters.

            Thank You again for your time.

  6. I require excel formula to eliminate the numerical value following each backslash \ in order to obtain the desired output as presented below.

    D:\Documents\3.0 Mexic\3.1 Vega\3.1.8 O&M\3.1.8.5 ESG\3.1.8.5.3 ESG C\3.1.8.5.3.2 NATUR\3.1.8.5.3.2.2 GUAJI\3.1.8.5.3.2.2.2 01_Pr\3.1.8.5.3.2.2.2.3 Anexo\3.1.8.5.3.2.2.2.3.2 Visit\3.1.8.5.3.2.2.2.3.2.2 P.S\3.1.8.5.3.2.2.2.3.2.2.2 PROGR

    output as below

    D:\Documents\Mexic\Vega\O&M\ESG\ESG C\NATUR\GUAJI\01_Pr\Anexo\Visit\P.S\PROGR

    • Hi! To remove all numbers and dots from the text, you can use the recommendations from the paragraph above: Delete multiple characters at once.

      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),".",""),0,""),9,""),8,""),7,""),6,""),5,""),4,""),3,""),2,""),1,"")

      However, you won't get the desired result as you don't want to remove all numbers and not all spaces and dots.

  7. I am looking for away to extract the date in each string.

    The names are like these:

    7_dfgdfh_20190425_8.JPG
    4_fhdrd_20190425_10.JPG
    524_ID289_sdf_sdg_sdf_20160607_(3).JPG
    473_ID4_sdf_sdg_20200405_(13).JPG
    7754_ID785_346I_20230809_(101).JPG

    Result:
    20190425
    20190425
    20160607
    20200405
    20230809

    I think RegExpExtract() is the answer because in each name there ist his pattern "_20******_",but Ican't figure it out.
    Any help? :D

  8. I am looking for a way to remove the last question mark from a offer url whenever there will be any ? Mark at the end of the offer url

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