Are you tired of manually changing the text case in your Google Sheets? Well, we have good news for you! Google Sheets comes with built-in functions that can effortlessly transform text to upper, lower, and proper case. What’s more, we will show you a handy trick to achieve sentence case using a custom function. Get ready to revolutionize your document editing experience!
How to Command Your Text with Built-in Functions
Let’s dive into the magic of Google Sheets’ built-in functions. With just a few simple steps, you can change the case of your text and make it dance to your tune.
To convert text to uppercase, simply use the UPPER function. By applying the formula =UPPER(A1), the text in cell A1 will transform into uppercase glory. You can also apply this formula to a whole column by utilizing the ARRAYFORMULA function: =ARRAYFORMULA(UPPER(A1:A)).
Likewise, Google Sheets offers the LOWER function to change text to lowercase. Just use the formula =LOWER(A1) to convert the text in cell A1. To convert an entire column, employ the ARRAYFORMULA function: =ARRAYFORMULA(LOWER(A1:A)).
But what if you want to capitalize the first letter of each word while keeping the rest lowercase? Fear not, for the PROPER function is here to save the day! Apply the formula =PROPER(A1) to convert the text in cell A1 to proper case. And of course, you can convert an entire column: =ARRAYFORMULA(PROPER(A1:A)).
Say Hello to Sentence Case with a Custom Function
Now, it’s time to tackle the elusive sentence case. While Google Sheets lacks a built-in function for this, we have a nifty custom formula that will do the trick.
Imagine you have a string of sentences in cell A1, and you want to capitalize the first letter of each sentence while keeping the rest unchanged. Use the following formula:
=ARRAYFORMULA(LET(
textA, A1,
testA, TOCOL(SPLIT(textA,". ",FALSE),1),
textB, TEXTJOIN(". ",TRUE,REPLACE(testA,1,1,UPPER(LEFT(testA,1)))),
testB, TOCOL(SPLIT(textB,": ",FALSE),1),
textC, TEXTJOIN(": ",TRUE,REPLACE(testB,1,1,UPPER(LEFT(testB,1)))),
testC, TOCOL(SPLIT(textC,"? ",FALSE),1),
IFERROR(TEXTJOIN("? ",TRUE,REPLACE(testC,1,1,UPPER(LEFT(testC,1)))), )))This formula cleverly splits the text into sentences using period, colon, and question mark delimiters. It then capitalizes the first letter of each sentence using the UPPER function. Finally, it rejoins the sentences together, keeping the delimiters intact.
TOSENTENCECASE Named Function
If you find yourself frequently converting text to sentence case, we have a special treat for you. Introducing the TOSENTENCECASE named function! To use this function, you need to import it from the source sheet provided below.
Once imported, you can effortlessly convert text to sentence case by applying the formula =TOSENTENCECASE(A1). Unlike the built-in functions, you don’t need to use the ARRAYFORMULA function to convert multiple cells. You can simply use the MAP function with a lambda function to convert an entire range.
=MAP(A1:A, LAMBDA(row, TOSENTENCECASE(row)))Remember to replace A1:A with the range of values you wish to convert.
And there you have it – a comprehensive guide to changing text case in Google Sheets. Now you can confidently conquer your documents with ease and style! Happy formatting!
