I want to sentence case every 4th row of a spreadsheet. Is it possible with a formula?
A not so elaborated way to do that:
Add two auxiliary columns: one with numbers in order (1, 2, 3…); other column with one cell ocuppied (could be with 1 or whatever symbol) and the other three cells empty (or 2 if the first cell has got 1).
Sort by the second column, apply the sentence case to the rows with 1, sort by the first column, delete the two auxiliary columns.
Shooting into the dark:
=IF(MOD(ROW()-1;4)=0;PROPER(A1);A1)
The 0 stands for 4,8,12,16,… 1 would stand for 1,5,9,13,17,…
Thanks for your response.
Please refer to the following sample csv for an example.
P1,abcd,abcd,abcd,abcd
,efgh,efgh,efgh,efgh
,hijk,hijk,hijk,hijk
,this row should be sentence-cased.
Exactly where should I use the formula you have given? I mean which cell should I select and apply the formula?
You can’t “sentence-case” a cell or a row. The tool is wrongly placed under the main menu item Format. It doesn’t assign a formatting attribute, but actually processes the current content.
If you want to do this for all the cells of some rows, you need to select all these rows first and to apply the tool then.
Wanting to select “every fourth row” (n-th row) in one go, you need to also define with which rows to start and to stop, and to write user code for the task. The next step (to apply “Sentece Case”) would then be done best by a recorded macro.
Once again: Anything entered into the processed cells later will not know about the SentenceCase thing.
There is no function for the task.
August 2
You can’t “sentence-case” a cell or a row. The tool is
wrongly placed under the main menu itemFormat. It
doesn’t assign a formatting attribute, but actually
processes the current content.
Okay, so you mean it’s not like emboldening the text or
italicizing it. It’s a function run on a cell. Right?
If you want to do this for all the cells of some rows, you
need to select all these rows first and to apply the tool
then. Wanting to select “every fourth row” (n-th row) in
one go, you need to also define with which rows to start
and to stop, and to write user code for the task. The next
step (to apply “Sentece Case”) would then be done best by
a recorded macro.
I absolutely have no idea how to write code for this. Can
you guide me to a tutorial regarding this? Or if easy can
you provide some code?
Once again: Anything entered into the processed cells
later will not know about theSentenceCasething.
No problem, I want to do it at once only. It shouldn’t be
active all the time.
Any kind of help will be highly appreciated.
Best,
निरंजन
Judging from your signature, you aren’t from the “western world”.
Transliterations like “to upper case”, “to sentence case”, “to title case”, however, will depend on the locale, I don’t know anything about subjects like “Asian” text or “Complex text layout”.
Are you sure you want the “Sentence case” to be applied under an English locale?
Questions how to do something “for every n-th row” are reocurring.
In cases where I knew more details, the supposed requirement of such a proceeding mostly was due to an unsuitable design of the sheet.
Please attach an example .ods allowing to understand the use-case.
Yes.
As I am a new user, I am not allowed to attach. Please use this example csv.
P1,abcd,abcd,abcd,abcd
,efgh,efgh,efgh,efgh
,hijk,hijk,hijk,hijk
,this row should be sentence-cased.
Sorry.
I didn’t have a problem with typing a few lowercase latin letters, nor with counting till 4.
For what I wanted to get an example was the eagerness to understand the situation.
As already told I suspect any sheet needing to do something “every n-th row” to be poorly designed. In such a case I would by far prefer to invest in a better design than to waste time with supporting a bad one.
Then: @Villeroy suggested to use the PROPER() function, and there was no objection. However PROPER() transliterates the first letter of each word to upercase, while SentenceCase is expected to transliterate only the first letter of each sentence (if applicable).
concerning the sentence case I already told that there isn’t a standard function for the purpose. The existing API means, however, are unhandy.
The question in a ddfferent comment
Exactly where should I use the formula you have given? I mean which cell should I select and apply the formula?
didn’t encourage me to continue on that way. Of course, there isn’t a function doing something every fourth row …
To automate a process of the kind you would need some enhanced user code.
Out of interest I wrote such code (still errors expected), but you only can make use of it, if you have a basic+ understanding of the ways macros are made available, and can be used.
If you think to be sufficiently prepared, please tell so. I will then attach an example containing the code and a bit more. .
I didn’t have a problem with typing a few lowercase latin letters, nor with counting till 4.
For what I wanted to get an example was the eagerness to understand the situation.
As already told I suspect any sheet needing to do something “every n-th row” to be poorly designed. In such a case I would by far prefer to invest in a better design than to waste time with supporting a bad one.
My entire document is like this only. It only has this pattern in a recurring manner. I don’t know how to determine whether my document is poorly designed or not. I am very new in using Calc. If you suspect my document is poorly designed, I would love to hear what are the most probable problems that you are suspecting.
Sorry. I don’t qualify. Thanks for your support till now though 
- You seem to have groups of 4 entries each. The elements of a group are supposed to be related. Such a relation is supposed to be represented by a range in a row spanning 4 columns.
- Wanting every fourth row to be transliterated in a specific way makes me assume that it is of a different category of meaning as compared with the other three rows.
- Keeping data in tables should regard some rules. A basical one is:
One sementical category :: one column. - Exemplified: DateTime, RoomTemperature, NumberOfPresentPeople, JokeThatWasTold are four semantical categories. A quadruple of them may form a relation named FunnyEvent. One Event per row should be a good idea. No every-forth-row-evaluation or re-formatting or whatever needed at any time.
- Even nearer to the question: Having the fourth category in its dedicated own column, it is very simple to apply
SentenceCasetio all the entries made there. Doing it for every fourth row is surprisingly complicated.
Instead of PROPER(A1):
=UPPER(LEFT(TRIM(A1);1))&MID(TRIM(A1);2;LEN(A1))
Replaces the first character with its upper case variant and appends the rest as is.
TRIM removes any leading and trailing spaces.