How to extract words that start with capital letters?

[brvnbld]

I have a column of Text data that has more than 300 words per cell. I am using MS Office 2021. I like to extract the words that start with capitalization from the cells. What is the formula for that?. Or is there any VBA script?

 

[brvnbld]

Give this a try. I have assumed data in column A starting in row 2 and results in column B.

Sub GetCapitalised_1()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "( [^A-Z][^ ]*)+"
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = Replace(RX.Replace(a(i, 1), " "), "  ", ", ")
    Next i
    .Offset(, 1).Value = a
  End With
End Sub

Here is my sample data and results

brvnbld.xlsm
	A	B
1	Data	Results
2	Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.	Lorem Ipsum, Lorem Ipsum, It, It, Letraset, Lorem Ipsum, Aldus PageMaker, Lorem Ipsum.
3
4	One capitalised words here	One
5	There are many variations of passages of Lorem Ipsum available.	There, Lorem Ipsum
Sheet1

Note that my code would not handle an example like this one of yours:

View attachment 68172

Excellent. It works. Thank you so so much guys. I never thought a forum is so helpful like this. Of course I have been using many forums, but many would tell me to post the question correct or withheld answers. You guys are so productive and helpful.

 

[Peter_SSs]

You're welcome. Glad to help. Thanks for the follow-up.

BTW, I had an extra 'RX.Pattern' line in the code I originally posted. It was there from my testing and I forgot to remove it, but I have edited it out now.
Not sure if you still have it in your code. It will not do any harm, but is not needed.

 

[brvnbld]

It was in the code, but anyways,. It worked well. Thanks again buddy for clearing that out.