Find text that is all caps

[Bekah]

I've been searching and can't find help. Ive learnt alot about title case etc but I'm in a quandry...

I have text field which is a mix of old program and new. Old program was all caps and new is right - entered as title case. i hate all caps. I should have fixed it before importing but I did not and now its too late.

I thought ... no prob ... just use field format to title case and it'll fix the all caps problem. But that has created its own problem. Because many title case names (companies) are like this:

The ABC Company ... or ... Whole Markets (GLD)

Using title case makes them wrong as The Abc Company and Whole Markets (Gld)

oh dear. now our staff enters them right and all caps is forbidden.

Question: can I somehow search for fields where EVERYTHING is all caps? If I can isolate those fields, I can use script to change them to title. then hopefully eliminate or reduce the wonkiness through the data. I know there will still be some to fix but that's okay. I think there are 10,000+ fields that are all cap. Make sense? I hope so. I hope there is help here. I don't know where to start.

One final thing. If I can get calc to identify all cap, I want to use it auto-entry or validation or flag error to staff - or unlock their caps lock. Ideas on this part also? Thank you for helping.

[Ender]

How about:

IsCAPS? (calculation; number result) = Exact(Field;Upper(Field))

Do a search for 1 in IsCAPS? to find those records where Field is in all caps.

Use a similar calc as the validation check to avoid all caps in future entries.

[Bekah]

Ender! Ender! Istant answer! And it works just right. I never would have thought of it. Ever.

Thank you Exact(SOOOOO MUCH;Upper(SOOOOO MUCH)) for helping!

[Bekah]

Ummmm, spotted similar problem ...

tHE bAXTER cOMPANY

Any hope in isolating these kinds - all caps where they tried to cap the first letter? I won't hold out hope but I thought i would ask. Appears that someone tried but had the lock on. Don't they ever look up at what they type?

I tried your formula but including - left(field;1) but couldn't get it accept it no matter what I tried.

[QuinTech]

Don't they ever look up at what they type?

No, not even when you spell it out for them. [sIGH]

Anyhow. It seems like there are 5 possible states for text:

1. ALL UPPER

2. all lower

3. rEVERSE tITLE cASE

4. soME MIxTurE of tHOSE

5. Title Case

Modifying Ender's original calc somewhat:

Not Exact ( Field ; Proper ( Field ) )

Doing the same search for 1 in this field will return anything that falls into categories 1, 2, 3, and 4 above. You could then replace with title case and individually edit any record that meets the exceptions you described.

HTH,

J

[Bekah]

Hello J Quin Tech

Thank you very much. This will work great exept when I use your wonderful calc I find way too many legit mixed case names. it'll be 50/50 on couple thousand that would need to be hand-corrected one way or other. Yuk. I don't mind trying to fix their errors but if I mess up correct names I'll get in trouble and have to manually fix myself. If I can't fix incorrect names (your #4), I'm probably not get blamed because I didn't type them. Do i sound selfish?

Can I isolate just the first letter small from the rest cap'd?? Can I get just 1-3? I don't mind having to find 3 times if necessary. I know record loops now.

I think I might be at optimum possible but you guys suprise me so thought I'd check.

Sorry edited ... I can get 1 & 2 (using Lower). I just want 3 now actually. probably 200 (wild guess, but a LOT to manually fix too!!). They'd be grateful if I fixed them for them and I'd be greatful to you for them being grateful.

[Bekah]

I just read recursion custom function. Can I use something like that to identify only tHE bAXTER cOMPANY? Otherwise I'm giving up on it. I tried finding just the left letter of each word being lower and the rest upper but couldn't get the formula to stop producing errors. why can't it tell me something more informative than "more '(' than ')' or more ')' than '(' ... geez

Spnt 6 hours today. OH well. Thank you all so much and I got everything else fixed (all cap, all lower). I knew my luck wouldn't hold on this one. Jerry? Ender? You both are wonderful!

[QuinTech]

I had given up on this, thinking that such a feat would require analyzing every letter of every word, until i realized that (assuming a certain level of consistency) you need only look at the first two letters of each word. I think, probably, this is the best you can do -- anything else becomes a horribly complicated calc, as you would need to calculate the length of each word and then analyze each letter within it. But, good news is, the first two letters can be done.

To determine the case of the first letter:

Upper ( 

  Left ( MiddleWords ( theField , 1 , 1 ) , 1 ) & 

  Left ( MiddleWords ( theField , 2 , 1 ) , 1 ) & 

  Left ( MiddleWords ( theField , 3 , 1 ) , 1 ) & 

  Left ( MiddleWords ( theField , 4 , 1 ) , 1 ) & 

  Left ( MiddleWords ( theField , 5 , 1 ) , 1 ) 

) 

= 

Left ( MiddleWords ( theField , 1 , 1 ) , 1 ) & 

Left ( MiddleWords ( theField , 2 , 1 ) , 1 ) & 

Left ( MiddleWords ( theField , 3 , 1 ) , 1 ) & 

Left ( MiddleWords ( theField , 4 , 1 ) , 1 ) & 

Left ( MiddleWords ( theField , 5 , 1 ) , 1 )

If the value of this calc is 1, the first letters are all upper case (note there is no provision for situations where some words are reverse title case and others are not; that's just way too difficult).





To determine the case of the second letter:

Upper ( 

  Middle ( MiddleWords ( theField , 1 , 1 ) , 2 , 1 ) & 

  Middle ( MiddleWords ( theField , 2 , 1 ) , 2 , 1 ) & 

  Middle ( MiddleWords ( theField , 3 , 1 ) , 2 , 1 ) & 

  Middle ( MiddleWords ( theField , 4 , 1 ) , 2 , 1 ) & 

  Middle ( MiddleWords ( theField , 5 , 1 ) , 2 , 1 ) 

) 

= 

Middle ( MiddleWords ( theField , 1 , 1 ) , 2 , 1 ) & 

Middle ( MiddleWords ( theField , 2 , 1 ) , 2 , 1 ) & 

Middle ( MiddleWords ( theField , 3 , 1 ) , 2 , 1 ) & 

Middle ( MiddleWords ( theField , 4 , 1 ) , 2 , 1 ) & 

Middle ( MiddleWords ( theField , 5 , 1 ) , 2 , 1 ) 

Again, if the value of this calc is 1, the second letter of each word (again, assuming the words are all consistent) is uppercase.

So if you searched for 0 in the first field and 1 in the second, this should find everything that is in reverse title case.

J

[Bekah]

I can do this! I will do it when I get home tonight!! Thank you - this was the last piece I couldn't fix (except for the really mixed up fields).

Am I the only one that deals with all cap and caps lock problems? I think using these calcs (yours and Ender's all cap calc) on field validation on every text field might be too much. I even considering having computer beep at them if 3 cap letters in a row are typed but don't know how. Is there any way that I can keep them from putting their caps lock on? I have Secure FM with MenuMagic but don't see caps lock listed?? I want to freeze their caps lock off.

You're the greatest, Jerry, for taking your time to help me solve this.

[QuinTech]

Never forget: Nothing is foolproof for a determined fool (Stephen Wright, i believe).

That said, you can check for Caps Lock being on when they launch the program with an opener script.

If ( Get ( ActiveModifierKeys ) = 2 )

Show Message ( "Please turn off your caps lock key!" )

End If

Of course, you could script this check to occur every time they change layouts, or however frequently you feel you need to, depending on your PEBCAK frequency.

J

Thanks, Bekah, but i have it on good authority that are a handful of people who are actually greater than me.

[Bekah]

Jerry Quin Tech! It worked! It worked! i ran it at lunch today and showed them! I just don't understand calcs very well and never would have done it without you. I'd be 105 years old covered with cobwebs and still sitting there trying to make it work.

I can't thank you and Mike Ender enough for helping me clean up this mess. You both took your important time to really help me. and you wouldn't beleive how much I learned from it. really. i learned a bunch!.

and God i love this little dancing bannana. it fits perfectly how i feel now.

oh forgot ... what is PEBCAK frequency?

[QuinTech]

PEBCAK

[Bekah]

Thanks for the great link!!!