Might I be lucky to find that there is an undocumented function of SentenceCap( or something similar?
The sentences that I need to capitalize are created from very long formulas and I’m not finding a simple way to do this without making the formula crazy long.
Perhaps Grep, or RegexReplace? Anyone smarter than I that can offer a RegexReplace to capitalize a sentence?
What I thought might work but does not is…
RegexReplace(ExtremelyLongFormulaThatResultsInText,'^(.)','\U\1')
upper(cache(ExtremelyLongFormulaThatResultsInText,“Temp”)[1,1])+Temp[2,-1]
The version of regex that Apple uses doesn’t seem to support \U, etc.
Panorama’s upper( function usefully supports all alphabetical characters — accented Latin, Greek, Cyrillic, etc. — not just A–Z.
WordService, freeware which you can get from the App Store, will allow you to do this in the Service menu of any application. Get CalcService while you are at it.
I appreciate the thought but this was needed to be done procedurally.
Thank you PCNewble. Good use of the cache( function.
I used that to extend your existing function call in its purest form, i.e. if you had no other reason to store the result of ExtremelyLongFormulaThatResultsInText in a variable.
Good use of the cache( function.
Agreed. But is it my imagination, or does Peter’s formula actually implement FirstLetterCap, not SentenceCap? I don’t think there’s any way to implement a SentenceCap( feature in Panorama. Maybe with a super complicated regular expression.
Might I be lucky to find that there is an undocumented function of SentenceCap( or something similar?
If I went to the trouble of implementing something as complicated as that I would definitely document it. Unless it didn’t work!
Good point. I think I assumed there was just one sentence.
How about:
arrayfilter(regexreplace(ExtremelyLongFormulaThatResultsInText,“. +”,". “),”. ",{upper(cache(import(),“Temp”)[1,1])+Temp[2,-1]})
That first standardises the number of spaces following each full stop (=period), in this case to one (but some might prefer two) then uses period-space as an array separator to split the result of ExtremelyLongFormulaThatResultsInText into sentences, then capitalises the first letter of each.
This still doesn’t catch every possible problem but would work for most prose.
Well, I think that what triggers recognition of a sentence is the beginning of a paragraph or a period, question mark or exclamation point followed by a white space. It is not going to be 100% accurate if there are abbreviations or something else that puts a period, etc. in the middle of a sentence, like this sentence has.
No need to use cache() in that case, of course, therefore this is better:
arrayfilter(regexreplace(“this is a sentence. and this is another. but this is yet another”,“. +”,". “),”. ",{upper(import()[1,1])+import()[2,-1]})
Good point. Try this:
replace(
arrayfilter(
regexreplace(
ExtremelyLongFormulaThatResultsInText,
"([\.\?\!]+\s+)",
chr(0)+"$1"+chr(0)
),
chr(0),
{upper(import()[1,1])+import()[2,-1]}
),
chr(0),
""
)
One or more periods, exclamation marks or question marks followed by one or more whitespace character (spaces, LFs, CRs or tabs) is sandwiched by NULs, then the first letter (if any) of each element in the NUL-separated array is capitalised, then the NULs are removed to leave the original separators between sentences intact.
Nor exclamation marks mid-sentence, e.g. ‘Lo! he comes with clouds descending’. But no such function can be perfect. If we assume the purpose of the original function is processing text so badly written that sentences frequently don’t have a capital letter at the beginning — otherwise why bother, for just the occasional outlier — then the assumption made here about what constitutes the beginning of a sentence will probably be an improvement more often than not, i.e. introduce fewer errors than it fixes.
I have dealt with things like this because my last name has a space in it. Actually, there are two official documents from when I was born, and one has the space and the other does not.
It is not going to be 100% accurate if there are abbreviations or something else that puts a period, etc. in the middle of a sentence, like this sentence has.
This is why I would never add a feature like this to Panorama itself. No matter what it did, it’s not going to work in all situations, and there would be complaints from people expecting magic.
This still doesn’t catch every possible problem but would work for most prose.
Yep. This is a good solution for this forum - just not built into Panorama.
It is difficult to predict all the circumstances that you will run across. That is a fundamental problem in designing databases. Coding is easy, comparatively.
I have a function that will take the all-caps result of looking up addresses on the post office ZIP code directory, and generally does a good job of converting it to upper and lower case, but still, I think I ran into one example where it did not work correctly.
Unfortunately, the service that I had been using to access the directory is going away, and that will make things much more difficult. (I am pretty sure that the zipinfoplus stuff that I wrote a long time ago has not worked for a long time, and I had been using a commercial API that was free and adequate for my purposes. But the USPS is changing their API to something cloud-based, and I have been informed that is going away. I have not figured out a replacement.)
My presumption was that if Panorama can assume responsibility for Sentence caps on initial entry, via the Data Entry/Auto Caps selection in the Field Properties dialog, and accept the flak from those that expect Panorama to be a mind reader and deliver pefection (in the eyes of the typist), that that same logic can be provided to the programmer. The code already exists in some shape or fashion within Panorama.
If you choose that option, you have made the decision, and you are responsible for what happens, That is different from setting something loose on your data willy-nilly. I do not choose capitalizing names because there are names that are not capitalized. My own last name is spelled without an initial capital by other families.
The programmer who does things willy-nilly deserve what they get.
Personally, I would like to have the tool in my toolbox so that I can be efficient when I want to screw up. To err is human, but to really foul things up requires a computer.
Bruce has hit the nail on the head. The capitalization assistant that Panorama currently has is an interactive tool, it works at the point of keyboard entry. If it doesn’t capitalize a letter correctly, then the user can immediately see that and correct it.
A bulk correction tool would have to perform to a much higher standard in terms of handling all of the possible cases, because there’s not going to be any possibility of comprehensive human review. Until recently I would have said that this was absolutely impossible, but now LLMs are approaching this sort of capability. Not quite there yet, but getting in the neighborhood. Though I personally think there is still quite a bit of the “willy nilly” aspect of LLM behavior.
The code already exists in some shape or fashion within Panorama.
It does not. The interactive feature only capitalizes a single letter at the end of text. Even if you insert a new character in the middle, it is not auto-capitalized (this is a feature, IMO, since it allows you to easily override the capitalization when needed). It doesn’t re-capitalize an entire sequence of text.
Personally, I would like to have the tool in my toolbox so that I can be efficient when I want to screw up.
As demonstrated by @pcnewble, Panorama does give you the toolbox to powerfully screw data up in the exact way you want it screwed up. But there’s a bit of a guardrail in that you’ll need to build your own “screw up the data” feature by building on the lower level tools that are available.