removing special characters
 

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......

--
JT

removing special characters
 

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

removing special characters
 

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

"JT" wrote:

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......

--
JT

removing special characters
 

On Wed, 12 Dec 2007 07:01:02 -0800, JT wrote:

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

removing special characters
 

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

"JT" wrote:

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......

--
JT

removing special characters
 

On Thu, 31 Jan 2008 15:14:01 -0800, Abbey Normal
wrote:

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

"JT" wrote:

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......

--
JT

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

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