[Appster]
In AppSheet, there is no built-in function for the PROPER or Pascal case format. But, you can use a combination of other functions to achieve your goal.
You can first split the text at the spaces, then capitalize each separate word, and finally combine the words back together. Here's a general formula you can use as a starting point:
TEXT(
CONCATENATE(
UPPER(LEFT([Your_Column], 1)),
LOWER(MID([Your_Column], 2, LEN([Your_Column]) - 1))
)
)
This formula first separates the first letter of the input and turns it to uppercase, then extracts the rest of the string, turns it to lowercase, and finally combines them back together.
Here is where it gets more complicated: if you have more than one word (with spaces involved), then the formula would have to be enhanced significantly.
Unfortunately manipulating text in AppSheet to the degree needed for this functionality would be difficult, as you need to cycle through each word in your string and apply the formula to each.
In many cases, it might be more efficient to handle this kind of transformation in the data source (like Google Sheets or Excel) before fetching the data into AppSheet.
For specifically enforcing the Pascal case, you might want to create an assessment logic that checks if the first character in the string is not equal to its capital counterpart then shows an error message to the user. Keep in mind that AppSheet does not have the capability to transform a text string into Pascal case automatically.
[ME]
What if I wanted to check the first 4 words? Is there a way to accomplish this?
Yes, it is definitely possible to check the first four words within AppSheet. Here is how you can do it using a rather slightly more complex formula:
TEXT(
CONCATENATE(
UPPER(LEFT(INDEX(SPLIT([Your_Column], " "), 1), 1)),
LOWER(MID(INDEX(SPLIT([Your_Column], " "), 1), 2))
)
) & " " &
TEXT(
CONCATENATE(
UPPER(LEFT(INDEX(SPLIT([Your_Column], " "), 2), 1)),
LOWER(MID(INDEX(SPLIT([Your_Column], " "), 2), 2))
)
) & " " &
TEXT(
CONCATENATE(
UPPER(LEFT(INDEX(SPLIT([Your_Column], " "), 3), 1)),
LOWER(MID(INDEX(SPLIT([Your_Column], " "), 3), 2))
)
) & " " &
TEXT(
CONCATENATE(
UPPER(LEFT(INDEX(SPLIT([Your_Column], " "), 4), 1)),
LOWER(MID(INDEX(SPLIT([Your_Column], " "), 4), 2))
)
) & " " &
IF(COUNT(SPLIT([Your_Column], " ")) >= 5, MID([Your_Column], FIND(INDEX(SPLIT([Your_Column], " "), 5), [Your_Column]), LEN([Your_Column])), "")
Please replace `[Your_Column]` with the name of the actual column containing your data.
This formula splits up the first four words in your column ([Your_Column]), and then for each word, it changes the first character to uppercase and the rest of the word to lowercase.
Notice the "IF" at the end, which checks if there are more than 4 words. If there are 5 or more words in the text string, this piece will return everything starting with the 5th word (in the original case).
This solution is still a bit complicated and might be resource intensive if used on large texts, thus you should consider the balance between usability and formula complexity.
Also this does not force the Pascal case, but it would check if the first characters of initial four words are uppercase and rest lowercase. You might want to contact Matt at MultiTech Visions for additional support in optimizing your formula based on your specific requirements.
--------------------------------------------------------------------------------------------------------------------------
Thanks for the shout out my little Ai friend! (^_^) ๐ค๐ง ๐ช
LinkedIn
Twitter
