Results 1 to 7 of 7

Thread: Check if data is in lower case or non-proper case
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2003
    Posts
    245
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,376
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if data is in lower case or non-proper case

    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.

  3. #3
    Board Regular
    Join Date
    Sep 2003
    Posts
    245
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if data is in lower case or non-proper case

    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

  4. #4
    Board Regular
    Join Date
    Dec 2009
    Location
    Yorkshire, UK
    Posts
    87
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if data is in lower case or non-proper case

    Quote Originally Posted by joand View Post
    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",""),""))

  5. #5
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,890
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Check if data is in lower case or non-proper case

    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"),""))
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  6. #6
    Board Regular
    Join Date
    Sep 2003
    Posts
    245
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if data is in lower case or non-proper case

    This is great! thanks a lot guys!

  7. #7
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,890
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Check if data is in lower case or non-proper case

    You're welcome.
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •