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)

Function WordsToLower(str As Variant) As String
    Dim i As Integer, sNewStr As String, sTmp As String
    For i = 0 To UBound(Split(str, " "))
        sTmp = Split(str, " ")(i)
        If UCase(sTmp) = sTmp And Len(sTmp) > 3 Then
            sNewStr = sNewStr & " " & LCase(sTmp)
        Else
            sNewStr = sNewStr & " " & sTmp
        End If
    Next i
    WordsToLower = Trim(sNewStr)
End Function

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.