Remove disallowed characters from a text field

The arguments for the Part function are all the delimiter characters you want to trap.  So you can do this



Part([Project Name],1," !@#$%^&*';:?/><")

But, I don't know how to trap a quote  ".  I will do a quick test now to see if a "" will do it.  

This seemed to work to trap a quote.  the backslash acts as as an escape character.  But next  you will be wanting to trap the backslash too!  I think that you use a double \ to do that.

Part([Part entry],1,"\";@#$\\/")

This is perfect! Thank you so much!

Does your dog do the QuickBase work and you take care of the dog or is it the other way around?

Use IOL:

Remove Disallowed Characters
https://haversineconsulting.quickbase.com/db/bmj2gmzcs?a=nwr

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=556

It is best to prevent the disallowed characters from entering your application during keyboarding rather than after the fact using formulas.

If you or your dog need additional assistance implementing this solution feel free to contact me off-world using the information in my profile:

https://getsatisfaction.com/people/dandiebolt

In QuickBaseCoach's answer... does that one line capture all of them? or in this case, do you have to still run through the Part 9 times?

Yes, you need to have that long Part formula for as many "parts" that there could be, where a part is delimited by any of those trap characters.

It might help to understand what the part function does. It takes an input and divides it into parts, and the 'fenceposts' it uses to divide, is what you define as the 'delimiter'.

so

Part([Project Name],1," ") &  Part([Project Name],2," ")

says:
'divide the field into parts defined by spaces. get the first part.'
'and then append to that'
'divide the field into parts defined by spaces. get the second part.'

in this context, that's simply performing the handy function of taking out all the characters you define as delimiters in your part function, and thus serving as a replace/remove kind of function. So you could make it say

Part([Project Name],1," -") &   Part([Project Name],2," -")

which does the same thing except it uses - as well as [space] to part out your input.

etc.

So the reason the part function is run through 9 times is because doing it once doesn't REMOVE or REPLACE the offending characters, it just stops when it gets to one. So in Mark/Coach's answer to the previous poster, if you only use the one line he posted, you'll just get whatever is in your field until it finds the first character you don't like. You have to repeat the part function a number of times to get the 'replace' functionality you're actually looking for.

Does that help?


I guess somehow I never realized until today that QB doesn't have an equivalent to what's in Excel called SUBSTITUTE, or, apparently, any kind of string transformation function. Using Part() this way is a pretty cool workaround.

Also, I expect your question was answered more succinctly while I was typing all this out. I'm posting it anyway.