Remove Text in Excel Without Macros

One of the benefits to having an Excel website is the interesting questions that I get in my email. I can’t help with every request, but some lead to blog posts or articles on my Contextures website.

Last week, someone asked how to clean up a column of text, by doing the following:

  • Remove all text AFTER a colon “:”, including the colon
  • Remove all text BEFORE any instance of “of” E.g., “Law Offices of”
  • Remove all text AFTER a dash “-”, including the dash?

findreplace01

What would you do? My first thought was Text to Columns – the text strings could be split at a colon or dash. It wouldn’t help with the “of” problem though, without a few extra steps.

Use a Wildcard

After thinking about this for a couple of minutes, I realized that Find and Replace would do a much better job than Text to Columns. You can use an asterisk (*) wildcard in the Find string, and that makes it very flexible.

The * wildcard represents any number of characters, and you can use it anywhere in the Find string.

First, I want to remove any colon, and all the characters after the colon.

  • Select column A, and use the keyboard shortcut Ctrl + H to open the Find and Replace window.
  • In the Find box, type a colon and an asterisk (Shift + 8): :*
  • Leave the Replace With box empty, because you just want to remove the characters, not replace them with anything
  • Click Replace All

Use Spaces With the Wildcard

Follow the same steps to remove hyphens, and any text after them. We want to remove the space too, so include that.

  • In the Find box, type a space and hyphen, followed by an asterisk: [space]-*

Follow the same steps to remove the word “of”, and any text before it. Be sure to include space characters before and after the “of”.

  • In the Find box, type an asterisk, followed by a space, “of” and another space : *[space]of[space]

The Cleaned Up Text

After a few seconds of finding and replacing, the text is cleaned up. If you have to do this type of cleanup frequently, you could create a macro to do the steps. However, for a one-off job, this is a quick way to clean things up.

findreplace03

For other ways to work with text, you can visit the TEXT function page on my website.

Watch the Video

To see the steps for using a wildcard with Find and Replace, please watch this short video.

______________

5 comments to Remove Text in Excel Without Macros

  • Rajan

    I want help on excel formula.

    I have a Text field called I in cell E17
    I have a Text Field called Yes/No in cell E18

    I want formula in F18 as under

    If E17= Yes and if E18 = I then 2690 else 2790, if E17 = yes and E18 = C then 3190 else 3290

    I tried with this formula but giving #value error
    =IF(AND(E18="Yes",E17="I"),2690,2790),IF(AND(E18="Yes",E17="C"),3190,3290)

    Please help me in getting this formula.

    Thank you.

  • Rajan

    IGNORE EARLIER ONE
    SLIGHT CORRECTION:

    I want help on excel formula.

    I have a Text Field called "Yes" or "No" in cell E17
    I have a Text field called "I" or "C" in cell E18

    want formula in F18 as under

    If E17= "Yes" and if E18 = "I" then 2690 else 2790, if E17 = "Yes" and E18 = "C" then 3190 else 3290

    I tried with this formula but giving #value error
    =IF(AND(E17="Yes",E18="I"),2690,2790),IF(AND(E17="Yes",E18="C"),3190,3290)

    Please help me in getting this formula.

    Thank you.

  • Toan

    It's interesting to me! Thanks!

  • Dave Roberts

    If the values in E17 are restricted to Yes and No and the values in E18 are restricted to I and C, this nested If works. The last If returns "One of your values is incorrect" when you get an unexpected input in E17 or E18.

    =(IF(AND(E17="Yes",E18="I"),2690,(IF(AND(E17="Yes",E18="C"),2790,IF(AND(E17="No",E18="I"),3190,IF(AND(E17="No",E18="C"),3290,"One of your values is incorrect"))))))

  • Rajan

    Thank you Dave Roberts sir,

    Its working well. My work in done. Thanx.

    Instead of "One of your values is incorrect" i just typed 0 (zero)

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>