Please enable JavaScript to view the comments powered by Disqus.

IFERROR(...INDEX(...MID(...\$A2,ROW(...INDIRECT(..."\$A1:\$A"&LEN(...\$A2))),1),SMALL(...IF(...ISNUMBER(...MATCH(...CODE(...MID(...\$A2,ROW(...INDIRECT(..."\$A1:\$A"&LEN(...\$A2))),1)),ROW(...\$A\$65:\$A\$90),0)),ROW(...INDIRECT(..."\$A1:\$A"&LEN(...\$A2))),"…"),COLUMN(...A1))),"")

How to extract only capital letters to different cells

This formula checks for the code of the character to identify it and then extracts only those letters that are capital.

Used Functions

Category: Logical

Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula

Syntax

IFERROR(value, value_if_error)

Category: Lookup and reference

Uses an index to choose a value from a reference or array

Syntax

INDEX(array, row_num, [column_num])

Category: Text

Returns a specific number of characters from a text string starting at the position you specify

Syntax

MID(text, start_num, num_chars)

Category: Lookup and reference

Returns the row number of a reference

Syntax

ROW([reference])

Category: Lookup and reference

Returns a reference indicated by a text value

Syntax

INDIRECT(ref_text, [a1])

Category: Text

Returns the number of characters in a text string

Syntax

LEN(text)

Category: Statistical

Returns the k-th smallest value in a data set

Syntax

SMALL(array, k)

Category: Logical

Specifies a logical test to perform

Syntax

IF(Something is True, then do something, otherwise do something else)

Category: Information

Returns TRUE if the value is a number

Syntax

ISNUMBER(value)

Category: Lookup and reference

Looks up values in a reference or array

Syntax

MATCH(lookup_value, lookup_array, [match_type])

Category: Text

Returns a numeric code for the first character in a text string

Syntax

CODE(text)

Category: Lookup and reference

Returns the column number of a reference

Syntax

COLUMN([reference])