VBA or formula to give lower case words from string

[redspanna]

Hey all

Through column B I have a list of strings with a mix of lower and upper case words, example

BIG RIPE apple
small yellow BANANA
green FRUIT WITH PIPS

I'd like a formula or vba that will only display the lower case words from the given string, d=so from above examples the results would be...


apple
small
green

TIA

 

[Rick Rothstein]

Assuming you are correct and that mixed letter case words should also be removed along with all upper case words, give this a try...

Function LCtext(ByVal S As String) As String
  Dim X As Long, Arr() As String
  Arr = Split(S)
  For X = 0 To UBound(Arr)
    If Arr(X) Like "*[!a-z]*" Then Arr(X) = ""
  Next
  LCtext = Application.Trim(Join(Arr))
End Function

Actually, I can compact this macro even more...

Function LCtext(ByVal S As String) As String
  Dim V As Variant
  For Each V In Split(S)
    If Not V Like "*[!a-z]*" Then LCtext = Trim(LCtext & " " & V)
  Next
End Function

 

[EXCEL MAX]

If you can hear I'm clapping hands both of you.
I was supposed that is something possible to be done with regEx, but code do not exclude numbers.
Mr. Rothstein's code is maestral use of the split and join functions.
This is so simple and beautiful.
In the name of the Redspana who is not interested about his question any more...
Thank you.

 

[Peter_SSs]

but code do not exclude numbers.

There is no requirement in the OP's post to exclude numbers. And who is to say whether a number is upper case or lower case?

In the name of the Redspana who is not interested about his question any more...

That is a bit harsh. There is no requirement for OP's to respond instantly, or in fact at all.