• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SumProduct to get Row and Column where Text has Special Character

S

SteveT

New Member
I love sumproduct but have a hard time in implementation.


For instance, am trying to find the row and column for any set of data that contains a special character like a single quote or double quote other invalid characters.


If i put text in a1:a4 with a4 having a single quote in the text, using this formula,


=SUMPRODUCT(NOT(ISERROR(FIND(single quote,A1:A4)))>0*ROW(A1:A4))


Thoughts?

This gives me Zero instead of row 4.
 
The right side of the comparison

"0*ROW(A1:A4))"

is being evaluated before doing the "greater than" check.


To find a single quote:

=SUMPRODUCT((ISNUMBER(FIND("""",A1:A4)))*ROW(A1:A4))
 
Luke, how can you code the same SumProduct to look for Non-Printable Characters i.e. Code<=31 without having to put in all 31 finds? Can this be done in a SumProduct or does it need to be a VBA loop. (need to stay away from VBA for this sheet)
 
Hmm. I'm assuming the non printable character is not a stand alone, but could be anywhere in the cell? This gets complex, as you're asking the formula to check the "array" os characters within the cell against the entire array of cells. You mentioned the need to stay away from VB for the sheet, but could you use a UDF in a helper column?


Short UDF:


Function IsPrintable(r As String) As Boolean


IsPrintable = True

For i = 1 To Len(r)

If Asc(Mid(r, i, 1)) <= 31 Then

IsPrintable = False

End If

Next


End Function


Then you just have some column with formula:

=ISPRINTABLE(A1)


And your sumproduct would compare the helper column against whatever other critera/values you have.


EDIT:

Here's a link to a similar question. Perhaps you could plug one of the formula solutions from here into a larger SUMPRODUCT?


http://answers.microsoft.com/en-us/office/forum/office_2007-excel/find-specific-text-characters-in-a-cell/26c25002-571a-401d-9a10-413074cbc866
 
Later...

Here's a similar post I was able to find. Question only concerns a single cell, but if one of the functions works, you could plug it into a larger SUMPRODUCT I suppose.


http://answers.microsoft.com/en-us/office/forum/office_2007-excel/find-specific-text-characters-in-a-cell/26c25002-571a-401d-9a10-413074cbc866
 
Later...


Here's a similar question I found. It only concerns a single cell, but if one of the formula solutions work, you could plug that formula into a larger SUMPRODUCT formula.


http://answers.microsoft.com/en-us/office/forum/office_2007-excel/find-specific-text-characters-in-a-cell/26c25002-571a-401d-9a10-413074cbc866
 
You can use .xls format, as long as you don't reference any range beyond IV65536

Link:

http://us.generation-nt.com/answer/excel-2007-udf-cant-reference-row-below-65536-help-198099331.html
 
You must log in or register to reply here.
Back
Top