Replace varying invalid characters..

conradcliff · Mar 11, 2011

Hey guys, so I've been trying to figure this out for a while and my brain is fried..hopefully someone can point me in the right direction.

I have a user submitted form online that I import into excel. Using a macro I separate all the data using separators the are automatically added from Formtools.

The problem is that I name a PDF using the data from some of those fields and customers often enter invalid characters including trailing spaces that prevent vba from saving the file.

So, I need to try and create a macro that will replace any invalid file-name character.

From what I've seen so far the code I've found is only good for replacing a character in a specific location of the data..my characters will always be moving around.

If anyone can point me in a good direction I would be super appreciative!

mole999 · Mar 11, 2011

something like this

Code:

        If Sheets("reference lookup").Range("J5") <> "" Then
            Sheets("reference lookup").Range("J5").Value = UCase(Sheets("reference lookup").Range("J5").Value)
            Sheets("reference lookup").Range("J5").Value = Replace(Sheets("reference lookup").Range("J5").Value, "!", "1")
            Sheets("reference lookup").Range("J5").Value = Replace(Sheets("reference lookup").Range("J5").Value, "£", "3")
            Sheets("reference lookup").Range("J5").Value = Replace(Sheets("reference lookup").Range("J5").Value, "$", "4")
            Sheets("reference lookup").Range("J5").Value = Replace(Sheets("reference lookup").Range("J5").Value, "%", "5")
            Sheets("reference lookup").Range("J5").Value = Replace(Sheets("reference lookup").Range("J5").Value, "^", "6")
            Sheets("reference lookup").Range("J5").Value = Replace(Sheets("reference lookup").Range("J5").Value, "&", "7")
            Sheets("reference lookup").Range("J5").Value = Replace(Sheets("reference lookup").Range("J5").Value, "*", "8")
            Sheets("reference lookup").Range("J5").Value = Replace(Sheets("reference lookup").Range("J5").Value, "(", "9")
            Sheets("reference lookup").Range("J5").Value = Replace(Sheets("reference lookup").Range("J5").Value, ")", "0")
            Sheets("reference lookup").Range("J5").Value = Replace(Sheets("reference lookup").Range("J5").Value, "I", "1")
            Sheets("reference lookup").Range("J5").Value = Replace(Sheets("reference lookup").Range("J5").Value, "Z", "2")
            Sheets("reference lookup").Range("J5").Value = Replace(Sheets("reference lookup").Range("J5").Value, "S", "5")
            Sheets("reference lookup").Range("J5").Value = Replace(Sheets("reference lookup").Range("J5").Value, "|", "1")
            Sheets("reference lookup").Range("J5").Value = Replace(Sheets("reference lookup").Range("J5").Value, ".", "")
            Sheets("reference lookup").Range("J5").Value = Replace(Sheets("reference lookup").Range("J5").Value, ",", "")
            Sheets("reference lookup").Range("J5").Value = Replace(Sheets("reference lookup").Range("J5").Value, " ", "")
            Sheets("reference lookup").Range("J5").Value = Trim(Sheets("reference lookup").Range("J5").Value)
        End If

and yes I am aware it can be made smaller, code wise

Ruddles · Mar 11, 2011

Are you asking for a complete macro or just a suggested approach?

If the latter, the Replace function will replace characters in a string.

For example, to replace slashes and backslashes with underscores:-

Code:

sString=Replace(sString,"/","_")
sString=Replace(sString,"\","_")

To remove characters completely, specify a null for the third argument:-

Code:

sString=Replace(sString,Space(1),"")

That will remove all spaces.

Rather than removing all the known invalid characters, however, I'd suggest keeping all the known valid ones instead - just in case the users manage to find one you haven't thought of!

I suspect that using RegExp is the way to go with this but it's a function I haven't really got to grips with yet. If I were doing this I would probably do something like this:-

Code:

Sub Demo()
 
  Dim iPtr As Long
  Dim sString As String
  Dim iChar As String
 
  sString = "Hello! £$%^&*() 123 _=+"
 
  Debug.Print sString
 
[COLOR=blue]  iPtr = 1[/COLOR]
[COLOR=blue]  Do Until iPtr > Len(sString)[/COLOR]
[COLOR=blue]    iChar = Mid(sString, iPtr, 1)[/COLOR]
[COLOR=blue]    Select Case iChar[/COLOR]
[COLOR=blue]      Case "0" To "9", "A" To "Z", "a" To "z"[/COLOR]
[COLOR=blue]        iPtr = iPtr + 1[/COLOR]
[COLOR=blue]      Case "_", "-", "#"[/COLOR]
[COLOR=blue]        iPtr = iPtr + 1[/COLOR]
[COLOR=blue]      Case Else[/COLOR]
[COLOR=blue]        sString = Left(sString, iPtr - 1) & Mid(sString, iPtr + 1)[/COLOR]
[COLOR=blue]    End Select[/COLOR]
[COLOR=blue]  Loop[/COLOR]
 
  Debug.Print sString
 
End Sub

This is a demo, obviously - you'd just use the bit in blue (plus the Dims if you needed them).

conradcliff · Apr 4, 2011

So sorry for such a late reply, I get ridiculously busy...wow, thank you guys so much!!
I'm going to try these out on Tuesday and see how they work, I'll check back in and let you know!

conradcliff · Jul 15, 2011

Hey guys, finally got around to working with this code..
I ended up going the simple route that I could understand.
Here's what I did:

Code:

EventNameString = ActiveCell.Offset(0, 28)
    EventNameString = Replace(EventNameString, "/", " ")
    EventNameString = Replace(EventNameString, "\", " ")
    EventNameString = Replace(EventNameString, ":", " ")
    EventNameString = Replace(EventNameString, "*", " ")
    EventNameString = Replace(EventNameString, "?", " ")
    EventNameString = Replace(EventNameString, Chr(34), " ")
    EventNameString = Replace(EventNameString, "<", " ")
    EventNameString = Replace(EventNameString, ">", " ")
    EventNameString = Trim(EventNameString)
    ActiveCell.Offset(0, 28).Value = EventNameString

I also was able to clean up the rest of the code in that module quite a bit and I learned a lot too.

Couldn't have done it without you, thanks so much!!

GTO · Jul 16, 2011

Just another possible way...
Sub TryIt() Dim UserTypedBleeeepIWouldLikeFixed As String UserTypedBleeeepIWouldLikeFixed = "My*goofy#!file/\name" If ReplaceIllegals(UserTypedBleeeepIWouldLikeFixed, _ Chr(32), _ "#", "!", "/", "\", ":", "*", "?", Chr(34), "<", ">") Then MsgBox "New name is: " & UserTypedBleeeepIWouldLikeFixed End If End Sub Function ReplaceIllegals(ByRef CellText As String, _ ByVal NiceCharacter As String, _ ParamArray YuckyCharacters() As Variant) As Boolean Static REX As Object Dim n& Dim Model$ On Error GoTo I_Failed If REX Is Nothing Then Set REX = CreateObject("VBScript.RegExp") REX.Global = True End If For n = LBound(YuckyCharacters) To UBound(YuckyCharacters) Model$ = Model$ & "\" & YuckyCharacters

Next REX.Pattern = "[" & Model$ & "]" CellText = Trim(REX.Replace(CellText, NiceCharacter)) ReplaceIllegals = True I_Failed: End Function
Hope that helps,

Mark

mole999 · Jul 18, 2011

conradcliff said:
Hey guys, finally got around to working with this code..
I ended up going the simple route that I could understand.
Here's what I did:

Code:

EventNameString = ActiveCell.Offset(0, 28) EventNameString = Replace(EventNameString, "/", " ") EventNameString = Replace(EventNameString, "\", " ") EventNameString = Replace(EventNameString, ":", " ") EventNameString = Replace(EventNameString, "*", " ") EventNameString = Replace(EventNameString, "?", " ") EventNameString = Replace(EventNameString, Chr(34), " ") EventNameString = Replace(EventNameString, "<", " ") EventNameString = Replace(EventNameString, ">", " ") EventNameString = Trim(EventNameString) ActiveCell.Offset(0, 28).Value = EventNameString

I also was able to clean up the rest of the code in that module quite a bit and I learned a lot too.

Couldn't have done it without you, thanks so much!!

I'm using a variation on the above to clean some 14,000 enteries, too many different variations in the stored SQL to be able to do it on the fly as its copied to the excel. My data is mostly a six character unique reference AA45FR, sometimes two numbers have been issued for the data and they are held as AA45FR / AA47RT, which is fine. My issue becomes when clean bad text leaves " / A" as the lead three digits, obviously I can't just delete " / " as it's necessary to keep, and the A is most often a part of the follow on code.

What I need to do is EventNameString = Left(Replace(EventNameString, " / ", "")) but can't for the lift of me think how to get it to work

I would be happy for it to do a If Left(EventNameString) = " / ", Then delete the stroke

And clues or thoughts to kick me in the right direction would be much appreciated

Ruddles · Jul 18, 2011

conradcliff said:
Hey guys, finally got around to working with this code..
I ended up going the simple route that I could understand.
Here's what I did:

Code:

EventNameString = ActiveCell.Offset(0, 28) EventNameString = Replace(EventNameString, "/", " ") EventNameString = Replace(EventNameString, "\", " ") EventNameString = Replace(EventNameString, ":", " ") EventNameString = Replace(EventNameString, "*", " ") EventNameString = Replace(EventNameString, "?", " ") EventNameString = Replace(EventNameString, Chr(34), " ") EventNameString = Replace(EventNameString, "<", " ") EventNameString = Replace(EventNameString, ">", " ") EventNameString = Trim(EventNameString) ActiveCell.Offset(0, 28).Value = EventNameString

I also was able to clean up the rest of the code in that module quite a bit and I learned a lot too.

Couldn't have done it without you, thanks so much!!

For interest, you could cycle through an array of characters rather than have a separate statement for each one. Based on your code with my changes in blue:-

Code:

[COLOR=blue][B]    Dim vChar As Variant
[/B][/COLOR]    
    EventNameString = ActiveCell.Offset(0, 28)
    
[COLOR=blue][B]    For Each vChar In Array("/", "\", ":", "*", "?", Chr(34), "<", ">")
[/B][/COLOR]      EventNameString = Replace(EventNameString, [COLOR=blue][B]vChar[/B][/COLOR], " ")
[COLOR=blue][B]    Next vChar
[/B][/COLOR]    
    EventNameString = Trim(EventNameString)
    
    ActiveCell.Offset(0, 28).Value = EventNameString

Yours to do with as you wish!

mole999 · Jul 18, 2011

Thanks

I do have to keep " / " example

"AA43TR / AA54ED" is what I need

" / AA43TR / AA54ED" has to convert to "AA43TR / AA54ED"

i'm removing words that are added i.e Replace(() "Aardvark","")

its identifying the first three that match that pattern, that i'm trying deal with, whilst leaving others in the string

Replace varying invalid characters..

conradcliff

Board Regular

Excel Facts

mole999

Well-known Member

Ruddles

Well-known Member

conradcliff

Board Regular

conradcliff

Board Regular

GTO

MrExcel MVP

mole999

Well-known Member

Ruddles

Well-known Member

mole999

Well-known Member

Similar threads

Forum statistics

Share this page

Replace varying invalid characters..

Board Regular

Excel Facts

Well-known Member

Well-known Member

Board Regular

Board Regular

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock