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?
Give this a try. I have assumed data in column A starting in row 2 and results in column B.
VBA Code:
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.
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.
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.
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.