Formula to replace invalid filename characters

[tschultz]

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:
\ / : * ? < > |. 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 > symbol.

Route"66" = Route 66
IS>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

 

[vezerid]

tschultz,
have you tried the non-formula approach? Edit | Replace...

Does this help?
Kostis Vezerides

 

[Gord Dibben]

Public 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 "[0-9a-zA-Z]" Then _
sTemp = sTemp & sChar
Next i
.Value = sTemp
End With
sTemp = ""
Next rcell
End If
End Sub


Gord Dibben MS Excel MVP