Search code examples
excelexcel-formulaexcel-365filterxml

How To Extract The CAPITAL WORDS or BLOCK LETTER WORDS From A String In Excel

How to extract the capitalized full words from a string in excel ? Refer the first Image, I have used the following formula to extract the CAPITAL / BLOCK LETTER WORDS From a string in a cell, it works perfectly,

• Formula used in cell B2

=TEXTJOIN(" ",,
FILTERXML("<a><b>"&SUBSTITUTE(A2," ","</b><b>")
&"</b></a>","//b[translate(.,'abcdefghijklmnopqrstuvwxyz',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ')=.]"))

CAPITAL_LETTERS_IMAGE_ONE

The above formula works perfectly as longs as there is no numerical, but it doesn't give proper output when there are some numbers, refer the Image below, may be I am missing something, using O365

CAPITAL_LETTERS_IMAGE_TWO

Refer the cells those green colored backgrounds, it should bring only the CAPITAL WORDS but it carries also the numbers. What should be the right way here. Thank You!

Courtesy : I have learnt & used FILTERXML formula by following the post of JvdV Sir, and it really helped me a lot, Thank you very much Sir for this wonderful piece.!

Workbook_OneDrive_Link

Solution

  • As per the given sample data:

    =TEXTJOIN(" ",,FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s[translate(., 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', '')='']"))

    This would check when all uppercase alpha-chars are translated to nothing the node would equal nothing, meaning all characters were uppercase alpha.

    enter image description here