How do i make this formula not case sensitive?

**Ron Coderre** · 07-08-2012, 06:51 PM

If you use this variation, you won't need to use CTRL+SHIFT+ENTER to make it an array formula.
Regular formula:
=SUMPRODUCT(LEN(BO5:BO14)-LEN(SUBSTITUTE(UPPER(BO5:BO14),"U","")))

Is that something you can work with?

**Zargon** · 07-09-2012, 05:24 PM

Originally Posted by Ron Coderre

If you use this variation, you won't need to use CTRL+SHIFT+ENTER to make it an array formula.
Regular formula:
=SUMPRODUCT(LEN(BO5:BO14)-LEN(SUBSTITUTE(UPPER(BO5:BO14),"U","")))

Is that something you can work with?

Yes and no.
Yes, cause thanks for an actual solution.
No cause if i wanted to use it, i'd had to change way to much to make it worth it, it'd be easier to just change the lettering to capitals in the reports.
can't i modify the original formula?

**Ron Coderre** · 07-09-2012, 06:41 PM

I DID modify your original formula...
I used the UPPER function to force the text to be uppercase and I replaced SUM with SUMPRODUCT (eliminating the need to CSE the formula).

Perhaps you could do this...
• Find/Replace all: = and replace with: |=....that will convert all formulas to text
• Find/Replace all: SUBSTITUTE( and replace with: SUBSTITUTE(UPPER(
• Find/Replace all: ;"U" and replace with: );"U"
• Find/Replace all: |=SUM and replace with: =SUMPRODUCT

That would convert all of your formulas to the new version.

Is that something you can work with?