Check if data is in lower case or non-proper case

[joand]

How do I check the following:

1) in column B (beginning in Row 5, hence B5 to last row in column B), I have several data.
2) They begin with markups, e.g. _rfsti, _par1, _adseq, _rftxt, _rfeti followed by a blank space followed by a sentence (or numerical values)
Sample is shown below:

_adseq 1
_rfsti This is a sentence
_rftxt United States
_rfeti This is another sentence

3) check if the data begins with the markup "_rfsti" or "_rfeti", if it finds a match, do the following:
check if the first letter of the value is in lower case, if it is in lower case returns the value "First letter should be capitalized" in column C in the same row where it finds the match
if the first letter of the value is in upper case, returns blank

4) check if the data begins with the markup "_rftxt", if it finds a match, do the following:
check if ALL the cases of the value are in proper case, if it is NOT in proper case returns the value "Words should be in proper case" in column C in the same row where it finds the match
if the first letter of the value is already in proper case, returns blank value

Kindly guide me how to get this started.

 

[Comfy]

Can you clarify what you mean by "Value" in your question.

Is it the markup that needs to be capitalised or the sentence after it?

Both 3 & 4, based on your info, seem to be the same.

Capitalising the first letter is the same as Proper case.

 

[joand]

Hi Comfy:

to answer your question, in the example I provided above

_adseq 1
"1" is the value

_rfsti This is a sentence
"This is a sentence" is the value

_rftxt United States
"United States" is the value

_rfeti This is another sentence
"This is another sentence" is the value

To the other question, I only want to check the first letter for the first scenario, while for the second scenario, I need to check if the entire sentence is in proper case

 

[a massive zebra]

How do I check the following:

1) in column B (beginning in Row 5, hence B5 to last row in column B), I have several data.
2) They begin with markups, e.g. _rfsti, _par1, _adseq, _rftxt, _rfeti followed by a blank space followed by a sentence (or numerical values)
Sample is shown below:

_adseq 1
_rfsti This is a sentence
_rftxt United States
_rfeti This is another sentence

3) check if the data begins with the markup "_rfsti" or "_rfeti", if it finds a match, do the following:
check if the first letter of the value is in lower case, if it is in lower case returns the value "First letter should be capitalized" in column C in the same row where it finds the match
if the first letter of the value is in upper case, returns blank

4) check if the data begins with the markup "_rftxt", if it finds a match, do the following:
check if ALL the cases of the value are in proper case, if it is NOT in proper case returns the value "Words should be in proper case" in column C in the same row where it finds the match
if the first letter of the value is already in proper case, returns blank value

Kindly guide me how to get this started.

Enter into C5 and copy down:

=IF(OR(LEFT(B5,6)="_rfsti",LEFT(B5,6)="_rfeti"),IF(ISERROR(FIND(MID(B5,2,1),LOWER(MID(B5,2,1)))),"","First letter should be capitalized"),IF(LEFT(B5,6)="_rftxt",IF(ISERROR(FIND(MID(LEFT(SUBSTITUTE(B5,"_rftxt ",""))&MID(SUBSTITUTE(B5,"_rftxt ",""),FIND("#",SUBSTITUTE(SUBSTITUTE(B5,"_rftxt ","")&" *"," ","#",1))+1,1)&MID(SUBSTITUTE(B5,"_rftxt ",""),FIND("#",SUBSTITUTE(SUBSTITUTE(B5,"_rftxt ","")&" *"," ","#",2))+1,1),2,1),PROPER(MID(LEFT(SUBSTITUTE(B5,"_rftxt ",""))&MID(SUBSTITUTE(B5,"_rftxt ",""),FIND("#",SUBSTITUTE(SUBSTITUTE(B5,"_rftxt ","")&" *"," ","#",1))+1,1)&MID(SUBSTITUTE(B5,"_rftxt ",""),FIND("#",SUBSTITUTE(SUBSTITUTE(B5,"_rftxt ","")&" *"," ","#",2))+1,1),2,1)))),"Words should be in proper case",""),""))

 

[gaz_chops]

Try
=IF(OR(LEFT(B5,6)="_rfsti",LEFT(B5,6)="_rfeti"),IF(EXACT(MID(B5,FIND(" ",B5)+1,1),UPPER(MID(B5,FIND(" ",B5)+1,1))),"","1st letter should be capitalised"),IF(LEFT(B5,6)="_rftxt",IF(EXACT(MID(B5,FIND(" ",B5)+1,99),PROPER(MID(B5,FIND(" ",B5)+1,99))),"","Words should be in proper case"),""))

 

[joand]

This is great! thanks a lot guys!

 

[gaz_chops]

You're welcome.