removing special characters

[JT]

I have a spreadsheet with 8 columns. One of the columns is text and contains
name's (both individuals and businesses), comments, and other miscellaneous
info.

A cost center has a macro that converts those 8 columns in each row into a
standard length string. After it creates the string for each row, it opens a
new workbook and writes each string on a separate row and finally saves the
"new" workbook as a csv file.

The "new" csv file is then sent to another department where it is uploaded
into their system. Yesterday, there was a problem with the upload since it
contained a special character. (I'm currently finding out which ones are not
allowed.)

We have macros that cycle through all of the characters in a column (field)
and removes double quotes (") for instance. We just remove them. We do not
replace them with a space or anything else.

I really don't want to write 32 loops to check each row (1 field only) for
every special character. If there are 500 rows of data, it would have to run
16,000 loops to check that column for special characters.

Is there a better or easier way to remove all special characters from a
specific column? Thanks for the help......

 

[Keith74]

Just recorded this, should get you going

Columns("C:C").Replace What:="""", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

hth
Keith

 

[Smallweed]

You could use Edit, Replace in Excel (for the VBA equivalent just use the
recorder).

You could also do it with code cell by cell by using Replace, eg:

Dim rng As Range
(code to select relevant ranges in sheet)
For Each rng In Selection
rng.Value = Replace(rng.Value,Chr(34),"") 'this replaces inverted
commas/quotes
Next

 

[Ron Rosenfeld]

I have a spreadsheet with 8 columns. One of the columns is text and contains
name's (both individuals and businesses), comments, and other miscellaneous
info.

A cost center has a macro that converts those 8 columns in each row into a
standard length string. After it creates the string for each row, it opens a
new workbook and writes each string on a separate row and finally saves the
"new" workbook as a csv file.

The "new" csv file is then sent to another department where it is uploaded
into their system. Yesterday, there was a problem with the upload since it
contained a special character. (I'm currently finding out which ones are not
allowed.)

We have macros that cycle through all of the characters in a column (field)
and removes double quotes (") for instance. We just remove them. We do not
replace them with a space or anything else.

I really don't want to write 32 loops to check each row (1 field only) for
every special character. If there are 500 rows of data, it would have to run
16,000 loops to check that column for special characters.

Is there a better or easier way to remove all special characters from a
specific column? Thanks for the help......

It'd be easier to design if we knew what the special characters are, or what
they are not.

You could loop through the column using regular expression and remove all the
special characters (or retain the non-special ones).

====================================
Sub foo()
Dim c As Range
Dim re As Object
Const sPat As String = "[!@#$%^&*()""]" 'list of special characters

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

For Each c In Selection
c.Value = re.Replace(c.Text, "")
Next c
End Sub
====================================

Or, for example, if it is simpler to list the allowable characters, and let us
say that the only allowable characters are letters and digits, you could change
sPat in above to "[^A-Za-z0-9]"




--ron

 

[Abbey Normal]

Hi. Could you share your macro with me? I'm trying to eliminate those pesky
"" myself.
Thanks,

 

[Ron Rosenfeld]

Hi. Could you share your macro with me? I'm trying to eliminate those pesky
"" myself.
Thanks,

The "macro" was in this thread. Here's a copy of what I wrote before:

It'd be easier to design if we knew what the special characters are, or what
they are not.

You could loop through the column using regular expression and remove all the
special characters (or retain the non-special ones).

====================================
Sub foo()
Dim c As Range
Dim re As Object
Const sPat As String = "[!@#$%^&*()""]" 'list of special characters

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

For Each c In Selection
c.Value = re.Replace(c.Text, "")
Next c
End Sub
====================================

Or, for example, if it is simpler to list the allowable characters, and let us
say that the only allowable characters are letters and digits, you could change
sPat in above to "[^A-Za-z0-9]"


--ron