Forum Discussion
AlexCertificati
7 years agoQrew Cadet
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
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
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.
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.