Help needed with a formula to convert upper-case words only to lower-case

Hi,

Can you think of a formula that will convert upper-case words longer than three letters, into lower-case. Leaving the rest of the string unchanged?

For example,

Mary and John at IBM thought this was a GREAT OPPORTUNITY. Becomes:
Mary and John at IBM thought this was a great opportunity.

I have also tried using morefunc's REGEX.SUBSTITUTE but I couldn't get that to work either!

Any ideas or help would be appreciated, thanks,

Chris

is it a set few 3 letter uppercase words, or do they have a lot of variance? is there only one 3 letter word per string, plus name/place capitalizations?

(..any sample data?)

Hi,

Without VBA it would be quite tricky to string together an array formula to do this. It is MUCH easier to use a user defined function.
Just add it to a module in VBA (ALT+F11) and use it instantly in your sheets like this:

String in A1: Mary and John at IBM thought this was a GREAT OPPORTUNITY.
In B1 enter: =WordsToLower(A1)

Please Login or Register  to view this content.

Yipee!

Rudi thanks. It will be no surprise to you that your code worked perfectly.

Thank you very much.

Chris

(I can't see how you mark a thread as 'SOLVED', but solved it is!)

Thread tools menu above the first post, Chris.

My pleasure Chris...
Glad it's doing what you need.