formula to copy only capitalized WORDS from a cell

[emymeeky]

as simple as I can explain, If Cell A2 contains carrot MANGO banana , I want MANGO to be copied to cell B2.
Thanks.

[TheCman81]

Somemore sample data would be helpful if you could supply it?
How many words are going to be in each cell?
Is there a pattern on how they are displayed in the cell?

[Jakobshavn]

Try the following User Defined Function (UDF):

Please Login or Register  to view this content.

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=capsonly(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

and

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx

for specifics on UDFs

Macros must be enabled for this to work!

[emymeeky]

here is a sample file.I want to be automated to extract capitalized letters to column B once capital letters are entered in any cell in column A

[emymeeky]

I tried running the macro. See what i got

[Jakobshavn]

You must insert a module and put the UDF in that module. See the attached


Remember to enable macros!

[emymeeky]

Thank you sir! you're just perfect!

[Jakobshavn]

You are quite welcome!

[emymeeky]

Actually am marveled by the fast response. I was really afraid if my problem was solveable.

Alright... what if I want to copy lowercase words to column c? guess I should add another module but don't know the variables to change in the macro to suit this purpose.
Thanks

[Jakobshavn]

Just another UDF in the same module. See attached:

[emymeeky]

That was fantastic ! but a problem sir . I would want it to also want to extract words that begin with capital letters only.. Will be useful for me in extracting names of people.

sorry I only post when I see a need for a new feature.
Thanks

[Jakobshavn]

See the attached:

[emymeeky]

lack words to express my appreciation

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.