Trying to identify cells in ALL CAPS vs Title Caps

I have a sheet where I've pulled a list of titles that exist on a series of URLs in either all caps or title case. I need to be able to identify or sort by the lines that are in all caps so that they can be corrected - is this possible?

Best Answer

  • James Keuning
    James Keuning ✭✭✭✭✭
    Answer ✓

    Put your urls in a field called IN

    Create a field called OUT, and put this formula: =UPPER(In@row)

    Then create two fields called whatever, like, COUNT CAPS IN and COUNT CAPS OUT

    COUNT CAPS IN:

    =FIND("A", In@row) + FIND("B", In@row) + FIND("C", In@row) + FIND("D", In@row) + FIND("E", In@row) + FIND("F", In@row) + FIND("G", In@row) + FIND("H", In@row) + FIND("I", In@row) + FIND("J", In@row) + FIND("K", In@row) + FIND("L", In@row) + FIND("M", In@row) + FIND("N", In@row) + FIND("O", In@row) + FIND("P", In@row) + FIND("Q", In@row) + FIND("R", In@row) + FIND("S", In@row) + FIND("T", In@row) + FIND("U", In@row) + FIND("V", In@row) + FIND("W", In@row) + FIND("X", In@row) + FIND("Y", In@row) + FIND("Z", In@row)

    COUNT CAPS OUT:

    =FIND("A", Out@row) + FIND("B", Out@row) + FIND("C", Out@row) + FIND("D", Out@row) + FIND("E", Out@row) + FIND("F", Out@row) + FIND("G", Out@row) + FIND("H", Out@row) + FIND("I", Out@row) + FIND("J", Out@row) + FIND("K", Out@row) + FIND("L", Out@row) + FIND("M", Out@row) + FIND("N", Out@row) + FIND("O", Out@row) + FIND("P", Out@row) + FIND("Q", Out@row) + FIND("R", Out@row) + FIND("S", Out@row) + FIND("T", Out@row) + FIND("U", Out@row) + FIND("V", Out@row) + FIND("W", Out@row) + FIND("X", Out@row) + FIND("Y", Out@row) + FIND("Z", Out@row)

    If those two are the same, you have all caps.

    This is just adding up all of the caps in the original, and then counting the caps in an all caps version of the original, and comparing them.

Answers

  • marc4
    marc4 ✭✭✭✭

    A real lazy way to do this is export all your data to google sheets, use the free ablebits power tools to convert all the titles to lowercase with initial caps and then reload it into smartsheet.


    /marc

  • SarahA
    SarahA ✭✭

    Thank you @marc4 - The titles that are in all caps need to be corrected on the corresponding URL, not in SmartSheet. That's on me - I should have been clearer! I'm looking to sort/identify the ones that need that correction so that I can see what volume of updates are needed and have an actionable list of URLs.

  • James Keuning
    James Keuning ✭✭✭✭✭
    Answer ✓

    Put your urls in a field called IN

    Create a field called OUT, and put this formula: =UPPER(In@row)

    Then create two fields called whatever, like, COUNT CAPS IN and COUNT CAPS OUT

    COUNT CAPS IN:

    =FIND("A", In@row) + FIND("B", In@row) + FIND("C", In@row) + FIND("D", In@row) + FIND("E", In@row) + FIND("F", In@row) + FIND("G", In@row) + FIND("H", In@row) + FIND("I", In@row) + FIND("J", In@row) + FIND("K", In@row) + FIND("L", In@row) + FIND("M", In@row) + FIND("N", In@row) + FIND("O", In@row) + FIND("P", In@row) + FIND("Q", In@row) + FIND("R", In@row) + FIND("S", In@row) + FIND("T", In@row) + FIND("U", In@row) + FIND("V", In@row) + FIND("W", In@row) + FIND("X", In@row) + FIND("Y", In@row) + FIND("Z", In@row)

    COUNT CAPS OUT:

    =FIND("A", Out@row) + FIND("B", Out@row) + FIND("C", Out@row) + FIND("D", Out@row) + FIND("E", Out@row) + FIND("F", Out@row) + FIND("G", Out@row) + FIND("H", Out@row) + FIND("I", Out@row) + FIND("J", Out@row) + FIND("K", Out@row) + FIND("L", Out@row) + FIND("M", Out@row) + FIND("N", Out@row) + FIND("O", Out@row) + FIND("P", Out@row) + FIND("Q", Out@row) + FIND("R", Out@row) + FIND("S", Out@row) + FIND("T", Out@row) + FIND("U", Out@row) + FIND("V", Out@row) + FIND("W", Out@row) + FIND("X", Out@row) + FIND("Y", Out@row) + FIND("Z", Out@row)

    If those two are the same, you have all caps.

    This is just adding up all of the caps in the original, and then counting the caps in an all caps version of the original, and comparing them.

  • SarahA
    SarahA ✭✭

    Thank you, @James Keuning! This seems to have worked! I added an additional column that would identify the matches and label them so that we can filter the data and we seem to be good to go. The weird thing is that once I moved into the larger sheet with more data, the =FIND columns came up with really high numbers even though they should have been just looking at the one cell@row, but the match/no match still seemed to be accurate so I'm going with it.

    Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!