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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
mole999

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
Ruddles

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,815
Office Version
  1. 365
Platform
  1. Windows
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
C

conradcliff

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

conradcliff

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

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,155
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
mole999

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
Ruddles

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,815
Office Version
  1. 365
Platform
  1. Windows
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
mole999

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

Z
  • Solved
VBA Loop Cells.Replace Multiple Characters (Case Sensitive)
Replies
4
Views
174
Alex Blakenburg
A
R
  • Solved
Data Validation not working how I want or I'm not using it correctly.
Replies
3
Views
141
Fluff
Fluff
Zaigham
  • Solved
Replace invisible characters from a range of cells
Replies
2
Views
197
Zaigham
Zaigham
G
  • Question
Help with writing VBA for data entry form
Replies
2
Views
90
GA036872
G
S
  • Solved
Removing invisible characters from VBA array and dynamic formula
2 3
Replies
23
Views
735
Spencer500
S

Forum statistics

Threads
1,191,559
Messages
5,987,290
Members
440,089
Latest member
FahadSid

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
Top