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]

For your second example, why isn't the answer "small yellow" instead of what you wrote ("small" by itself)?

Could there be other text (I'm thinking numbers and punctuation that you want or don't want in the output?

 

[redspanna]

For your second example, why isn't the answer "small yellow" instead of what you wrote ("small" by itself)?

Sorry, Typo in example, correct results would be

apple
small yellow
green

....and there will not be any numbers or punctuation in the original test string, so don't need these in output

thanks

 

[Rick Rothstein]

Here is a UDF (user defined function) that you can use...

Function LCtext(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[A-Z]" Then Mid(S, X) = " "
  Next
  LCtext = Application.Trim(S)
End Function

Note: I can make this into a macro if you wish to run it that way, just let me know the starting row number.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use LCtext just like it was a built-in Excel function. For example,

=LCtext(B2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[EXCEL MAX]

What about mixed words in format "Apple" or "Small Yellow"?

 

[EXCEL MAX]

I see no one interested to continue discussion,
but I think that will be maybe useful to have function whose exclude even words with mixed characters from text
and leave only lower case.
I don't know is there some short way to do that, so I bring this one UDF...

Option Explicit

Dim varNLoops, varNLoops2, varSpace As Integer
Dim varWord, varTempWord, varTempWord2, varFinalWord, _
    varChar, varTempChar As String

Function CutLCase(varRange)

    varTempWord = " " & varRange & " "
    For varNLoops = 1 To Len(varRange)
        varSpace = InStr(1, LTrim(varTempWord), " ")
        If varSpace = 0 Then GoTo E2
        varWord = Mid(LTrim(varTempWord), 1, varSpace)
        varTempWord = Mid(Trim(varTempWord), varSpace, Len(varTempWord)) & " "
        For varNLoops2 = 1 To Len(varWord) - 1
            varTempChar = Mid(varWord, varNLoops2, 1)
            If varTempChar Like "[a-z]" Then
                varTempWord2 = varTempWord2 + varTempChar
            Else
                varTempWord2 = ""
                GoTo E1
            End If
        Next
        varFinalWord = varFinalWord & " " & varTempWord2 & " "
        varTempWord2 = ""
E1: Next
     varFinalWord = Trim(varFinalWord)
     varTempWord2 = ""

E2: CutLCase = Trim(varFinalWord)
    varFinalWord = ""
    
End Function

 

[Rick Rothstein]

I see no one interested to continue discussion,

I was waiting for the OP to come back and respond to your question about mixed letter casing to see if he wanted to keep them or reject them.

 

[EXCEL MAX]

Ok, Mr Rothstein.
I'm impressed with your previous short solution.
Would you tell us do you have some kind of short solution about mixed letters.
My function looks too much complicated.

 

[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

 

[Peter_SSs]

What about mixed words in format "Apple" or "Small Yellow"?

I suspect that is not possible with the OP's data give the description "mix of lower and upper case words" (as opposed to "words with a mix of lower and upper case letters") and the given examples.

However, this alternative suggestion for a udf would reject mixed words as shown in row 6 below and still works for the original example types.

Function LowerOnly(S As String) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\b[^ ]*?[A-Z][^ ]*?\b"
    LowerOnly = Application.Trim(Trim(.Replace(S, "")))
  End With
End Function

redspanna.xlsm
	B	C
1	BIG RIPE apple	apple
2	small yellow BANANA	small yellow
3	green FRUIT WITH PIPS	green
4	green FRUIT xyz WITH PIPS inside	green xyz inside
5	BIG small tiny ant BUG	small tiny ant
6	BIG small Tiny ant BuG	small ant
Sheet1

Cell Formulas
Range	Formula
C1:C6	C1	=LowerOnly(B1)