Announcement

Collapse
No announcement yet.

Excel Capital Letter Filter

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel Capital Letter Filter

    I have a spreadsheet with 12000 rows where one of the columns has lines with words in capitals and words in lower case.

    What I need to do is extract the data from each line where all letters are in capitals and move it too another column. For example:

    red
    BLUE BLUE
    GREEN GREEN
    pink
    BROWN BROWN

    Does anybody know a formula on how to do this?

  • #2
    Re: Excel Capital Letter Filter

    You havent said which version of Excel you are using but in 2007 you could try this:
    Assuming your words are in A1 and belo, put the following formula in B1 and replicate:
    =IF(EXACT(A1,UPPER(A1)),A1,"")
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Re: Excel Capital Letter Filter

      2003, thought raising it in the XP / 2003 would be enough.

      This worked anyway, thanks for your help.

      Comment


      • #4
        Re: Excel Capital Letter Filter

        Glad it worked!
        Tom Jones
        MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
        PhD, MSc, FIAP, MIITT
        IT Trainer / Consultant
        Ossian Ltd
        Scotland

        ** Remember to give credit where credit is due and leave reputation points where appropriate **

        Comment

        Working...
        X