Hello Friends,
I am trying to extract a part of text and put it into another field.
For example I have a field called “Registration”, it might contain “WC-Whole Convention” or WCC-Whole Convention Comp" or “P-Pastors Registration”.
I would like to strip of everything after the hyphen and just keep the 1,2 or 3 letter short code in the beginning. I would like to put the results in a field called “Regis.Short”
Thanks for any help
CMTA, you haven’t specified if this is a question for Panorama 6 or for Panorama X. Generally this can be solved with text funnels (read about them in Panorama X Help or in Panorama Programming Reference).
This is a special case of text funnels, because the dash sign is an operator (as a minus sign) in text funnels. So your formula would first have to replace the dashes in your strings with a different character e.g. “•”; then you can use text funnels to get the results you want. Supposed a field “A” contains your text strings, this could be the code:
replace(A,"-","•")[1,"•"][1,-2]
The replace function replaces the dash with a dot; the first funnel extracts the first part of the string with the dot, the second funnel removes the last character, the dot.
Panorama X has an easier way to get your results. It offers a Split command that would split the field “A” at the dash in two fields “Left A” and “Right A”. “Left A” contains your result.
If you don’t want to make any changes to The Registration field, it’s a formula fill.
In a procedure it would be
Field «Regis.Short»
FormulaFill array(Registration,1,"-")
In Panorama X you find that dialog by clicking the Morph button on the tool bar.
The code for that would be
field Regis.Short formulafill replace(A,"-","•")[1,"•"][1,-2]
Pan X. Thanks. I tried the text funnels with not the desired results.
Or if you want to avoid jumping through hoops to change the hyphens, use :
field Regis.Short formulafill textbefore(A,"-")
Thanks. Using Pan X. I was thinking of creating the procedure in the code section of properties so when I enter the full entry it would then take that data and strip everything after the hyphen.
In that case, it wouldn’t be a FormulaFill. It would be just one statement.
«Regis.Short» = array(Registration,1,"-")
I think instead of using text funnels or an array( function it would be easier to use the before( function, which is available in both Panorama 6 and X.
Like this:
«Regis.Short» = before(Registration,"-")
This sounds so great and simple. That is exactly what I want to do. Thanks to everyone who weighed in on this. You are a help.
Jim’s Before function was simple to code and worked like a charm.
It prompted me to look at the difference between before( and textbefore(.
The only difference between before( and textbefore( is the way they treat the returned value if the tag is not found. The before( function will return an empty string while the textbefore( function returns the entire original text. Both of these use a tag parameter so you can specify one or more characters to set the selection.
Now that Panorama X accepts multiple characters as separators for use with the various array statements and functions you can treat them as tags as well as separators. This means that both of these functions can be done using an array( function in Panorama X.
textbefore(
current formula:
thetext[1,-1+search(thetext,thetag)]
array formula:
array(thetext,1,thetag)
before(
current formula:
tagdata(chr(255)+chr(254)+chr(253)+thetext,chr(255)+chr(254)+chr(253),thetag,1)
array formula:
?(arraysize(thetext,thetag)=1,"",array(thetext,1,thetag))
I mostly just wanted to point out that Panorama X arrays now can use multiple characters as the separator which vastly increases their usefulness.
Well, as I noted elsewhere, my latest attempt to use arrays ended in a terrible mixup. (I did write around that by eliminating the arrays, and now the database works, after a couple of crashes and a window that refused to close until I force quit.)