Replace varying invalid characters..

C

conradcliff

Board Regular
Joined
Feb 24, 2010
Messages
58
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! :)
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
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
 
Upvote 0
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).
 
Upvote 0
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! :)
 
Upvote 0
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!! :biggrin:
 
Upvote 0
Just another possible way...
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> TryIt()<br><SPAN style="color:#00007F">Dim</SPAN> UserTypedBleeeepIWouldLikeFixed <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    UserTypedBleeeepIWouldLikeFixed = "My*goofy#!file/\name"<br>    <br>    <SPAN style="color:#00007F">If</SPAN> ReplaceIllegals(UserTypedBleeeepIWouldLikeFixed, _<br>                       Chr(32), _<br>                       "#", "!", "/", "\", ":", "*", "?", Chr(34), "<", ">") <SPAN style="color:#00007F">Then</SPAN><br>                       <br>        MsgBox "New name is: " & UserTypedBleeeepIWouldLikeFixed<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br><SPAN style="color:#00007F">Function</SPAN> ReplaceIllegals(<SPAN style="color:#00007F">ByRef</SPAN> CellText <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _<br>                         <SPAN style="color:#00007F">ByVal</SPAN> NiceCharacter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _<br>                         ParamArray YuckyCharacters() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><SPAN style="color:#00007F">Static</SPAN> REX  <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> n&<br><SPAN style="color:#00007F">Dim</SPAN> Model$<br>    <br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> I_Failed<br>    <SPAN style="color:#00007F">If</SPAN> REX <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> REX = CreateObject("VBScript.RegExp")<br>        REX.Global = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> n = <SPAN style="color:#00007F">LBound</SPAN>(YuckyCharacters) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(YuckyCharacters)<br>        Model$ = Model$ & "\" & YuckyCharacters(n)<br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br>    REX.Pattern = "[" & Model$ & "]"<br>    CellText = Trim(REX.Replace(CellText, NiceCharacter))<br>    ReplaceIllegals = <SPAN style="color:#00007F">True</SPAN><br>I_Failed:<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>
Hope that helps,

Mark
 
Upvote 0
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!! :biggrin:
Click to expand...

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
 
Upvote 0
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!! :biggrin:
Click to expand...

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!
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
VBA to ignore certain MAC Address values but process all others
Replies
1
Views
183
myall_blues
myall_blues
The Ruff Report
  • Solved
VBA - replace a character in a range to save
Replies
5
Views
169
RobP
R
SandsB
  • Question
8192 character count limit wasn't there until my files were moved to Onedrive from a Shared Drive
Replies
6
Views
475
SandsB
SandsB
tourless
  • Question
Capture and remove last two in a string
Replies
3
Views
476
duggie33
D
J
  • Question
Find/Replace not working in cell with a lot of text
Replies
5
Views
798
Micron
M

Forum statistics

Threads
1,214,653
Messages
6,120,748
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top