software

I have a form being exported into Excel from an application where the
customer names may include any one or more of the following characters:
\ / : * ? lt; gt; |. I do not have the option of guaranteeing these
characters are not used since the customer data source is SAP. Since
these characters can not be used as part of a file name I'm struggling
with some code I have set up to perform a Save As at the end of a
formatting macro.

Say the customer name in cell A1 contains one or more invalid
characters, how do I remove or replace them with something else? I
currently have the formula in the macro replacing a / with a space, but
after further checking some of the customer names have two quotation
marks or the gt; symbol.

Routequot;66quot; = Route 66
ISgt;Runners Inc = IS Runners Inc

Ideally I would like the formula to check for any one or more of the
invalid characters and remove or replace them with something else to
make the new value a valid part of a file name. In a previous form,
not using macros, I performed each replacement of characters in a
separate cell and took the final cell as part of the filename, but I'd
like to perform this in one cell and record the formula for placement
into my macro.

I've tried several different ways with no luck and maybe there are
Excel limitations I am not aware of. I'm experienced with formula's,
but this one has me stumped and maybe it's not even possible.

Any replies would be greatly appreciated. I'm hoping someday I can
provide help to other users as this forum has answered many questions
I've searched for.

tschultz--
tschultz
------------------------------------------------------------------------
tschultz's Profile: www.excelforum.com/member.php...foamp;userid=7877
View this thread: www.excelforum.com/showthread...hreadid=505751tschultz,
have you tried the non-formula approach? Edit | Replace...

Does this help?
Kostis VezeridesPublic Sub StripAll_But_NumText()
Dim rConsts As Range
Dim rcell As Range
Dim i As Long
Dim sChar As String
Dim sTemp As String

On Error Resume Next
Set rConsts = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConsts Is Nothing Then
For Each rcell In rConsts
With rcell
For i = 1 To Len(.text)
sChar = Mid(.text, i, 1)
If sChar Like quot;[0-9a-zA-Z]quot; Then _
sTemp = sTemp amp; sChar
Next i
.Value = sTemp
End With
sTemp = quot;quot;
Next rcell
End If
End SubGord Dibben MS Excel MVP

On Fri, 27 Jan 2006 09:50:42 -0600, tschultz
gt; wrote:

gt;
gt;I have a form being exported into Excel from an application where the
gt;customer names may include any one or more of the following characters:
gt;\ / : * ? lt; gt; |. I do not have the option of guaranteeing these
gt;characters are not used since the customer data source is SAP. Since
gt;these characters can not be used as part of a file name I'm struggling
gt;with some code I have set up to perform a Save As at the end of a
gt;formatting macro.
gt;
gt;Say the customer name in cell A1 contains one or more invalid
gt;characters, how do I remove or replace them with something else? I
gt;currently have the formula in the macro replacing a / with a space, but
gt;after further checking some of the customer names have two quotation
gt;marks or the gt; symbol.
gt;
gt;Routequot;66quot; = Route 66
gt;ISgt;Runners Inc = IS Runners Inc
gt;
gt;Ideally I would like the formula to check for any one or more of the
gt;invalid characters and remove or replace them with something else to
gt;make the new value a valid part of a file name. In a previous form,
gt;not using macros, I performed each replacement of characters in a
gt;separate cell and took the final cell as part of the filename, but I'd
gt;like to perform this in one cell and record the formula for placement
gt;into my macro.
gt;
gt;I've tried several different ways with no luck and maybe there are
gt;Excel limitations I am not aware of. I'm experienced with formula's,
gt;but this one has me stumped and maybe it's not even possible.
gt;
gt;Any replies would be greatly appreciated. I'm hoping someday I can
gt;provide help to other users as this forum has answered many questions
gt;I've searched for.
gt;
gt;tschultz