Trying to identify cells in ALL CAPS vs Title Caps

SarahA

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?

James Keuning

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.

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

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

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

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!