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.
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.
I thus extract the piece of text starting after the " sign for a length of the " sign to the end of the string.
I actually use a macro to remove unwanted characters from Excel.
(If you know how to create a Macro, skip down to the code)
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, ""), " ", " ") r.Value2 = Replace(.Replace(r.Value2, ""), """, "") Next r End With End Sub
Where I have "
" and "
"", 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!