Find Count of Text [<=Char(32)] in a Range Non-VBA

[Steve=True]

I am looking for non-printable characters where char(<32) or <CHAR(32)AND characters special>Char(>126)

I have this formula

=SUMPRODUCT((ISNUMBER(FIND("~",A1:A4)))*ROW(A1:A4))

That finds one piece of text in a range, but i would like to find other characters where Char(<32) without having to write 32 different formulas.

Also, i can't use macros or VBA.

Any thoughts would be great.

Thanks!

 

[venkat1926]

do you want find non printable characters or remove them

Trim function will remove the spaces leading and forwardidng

you can also replace function in replacing such characters by blanks

there is a perarson's addin to view cell contents including non printable characters.l
http://www.cpearson.com/excel/CELLVIEW.ASPX

this is a good addin to add to your excel.
once you have installed this addin this sub menu will appear in view(menu bar)

 

[Steve=True]

Thanks, however, this is a solution that will be run on 2000 client desktops all with varying skills and macros/Vba may be denied by their IT depts.

I am trying to make an error checker that will show them if they have errors in their data.

I am using a combination of Data Validation and Conditional Formatting, but i need to create another error checker in case they Copy/Paste data which eliminates Data validation.

I am capable of find/replace, but looking for a formula solution.

Thanks again for your help!

 

[Peter_SSs]

A few lines of sample data and expected results might help. Since we are dealing with non-printable characters you may need to use some other character (eg underscore) in your samples and explain what each of those characters actually is.

 

[huutri]

Hi All

I have problem about list datavalidation: I want to add a space in first row of list validation, I try use chr(32) but it doesn't show any space. Can i help you add a space to first row in list datavalidation? Thanks