0

I have a long list of entries on a excel data sheet like:

1235. "email address",

How can I remove the in bold characters i.e. 1235. " and the ", from this.

The initial numbers go from 279 - 5000.

1

It is not so difficult using the correct functions. MID takes a part of a string starting a specified starting position for a specified number of characters. FIND finds a character in a text and returns the position. CHAR(34) is used to search for the " sign, because otherwise you would have to many "'s. LENGTH is used to find the length of the string.

=MID(B5,FIND(CHAR(34),B5)+1,LENGTH(B5)-(FIND(CHAR(34),B5)))

I thus extract the piece of text starting after the " sign for a length of the " sign to the end of the string.

0

I actually use a macro to remove unwanted characters from Excel.

(If you know how to create a Macro, skip down to the code)

To create a Macro, open your workbook and hit Alt+F11. This will open Visual Basic. On the top left, there will be a little button that looks like this: enter image description here Click the down arrow and select "Module"

Copy the following code and paste it into your blank Module window:

Sub RemoveTags()
    Dim r As Range

    Selection.NumberFormat = "General"  'set cells to text numberformat

    With CreateObject("vbscript.regexp")
      .Pattern = "\<.*?\>"
      .Global = True


    For Each r In Selection
        r.Value = Replace(.Replace(r.Value, ""), "&nbsp;", " ")
        r.Value2 = Replace(.Replace(r.Value2, ""), "&quot;", "")
      Next r

    End With
End Sub

Where I have "&nbsp;" and "&quot;", replace those with the characters you want to replace. In the quotation marks following each character, enter what you want them replaced with. In my example, the first line puts in a space, the second line replaces the characters with nothing (which is what I think you're going for).

You can copy / paste the sections from "For Each R In Selection" to "Next r" and enter more characters to replace if you need to.

Save and close Visual Basic.

To run the code, you're going to need to enable developer mode. Here's a link to a quick run-down on how to do that. Select the cells you want characters replaced in, click on the developer tab, and click on the "Macros" button. Select the "Remove Tags" macro and hit run.

I know VBA can be intimidating at first. But believe me, this code has helped me clear xml formatting from thousands of rows of data. Hope it helps!

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.