Search Upper Case

**VegasL** · 09-25-2008, 05:15 PM

Hello,

Please see attached file.

What I'd like to do is search column a , and if finds anywhere in text an uppercase within text, display entire text, else display ""

I tried =IF(ISERROR(FIND(" ",A2)),EXACT(A2,UPPER(A2))) but that didnt work.

any help would be appreciated

**VBA Noob** · 09-25-2008, 05:27 PM

Maybe

=IF(EXACT(UPPER(MID(A2,FIND("/",A2)+1,1)),MID(A2,FIND("/",A2)+1,1)),A2,"")

VBA Noob

**mikerickson** · 09-25-2008, 09:31 PM

This array formula doesn't depend on the / marker. And it is slower.
=IF(OR(ISNUMBER(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},A3))),A3,"")

Array formulas are confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)

**mdbct** · 09-25-2008, 10:52 PM

Here is a variation of mikerickson's formula. It is an array formula and must be confirmed with Ctrl+Shift+Enter.

Please Login or Register  to view this content.

If you use mikerickson's formula, make sure you remove the space after the "T "

Mike,
I used your formula without confirming it as an array and it calculated correctly.

**Mikeopolo** · 09-25-2008, 11:46 PM

I like the char(row(...)) construct, but you could also use
=CHAR(COLUMN(A:Z)+64)
which makes some visual sense too

**VegasL** · 09-30-2008, 06:10 PM

thanks guys appreciate ur help...

**daddylonglegs** · 09-30-2008, 06:47 PM

Why not just

=IF(EXACT(A2,LOWER(A2)),"",A2)

**Mikeopolo** · 09-30-2008, 08:25 PM

Why not indeed! great suggestion...

**mikerickson** · 10-01-2008, 02:44 AM

The EXACT/LOWER formula will display the string "+/-", which has no upper case letters.

If strings containing no letters are not an issue, that's a very good formula.