Counting Text String Less Than

[Newbienew]

I am looking for a formula that will allow me to count text less than 2 and exclude blank spacing. So if it is one letter it would be wrong and if its one letter with a space it will still be counted as wrong. 2 Characters is the min. Please help

 

[footoo]

=IF(LEN(SUBSTITUTE(A1," ",""))=1,"Wrong", "OK")

 

[JoeMo]

Your thread title seems to conflict with the statement "2 Characters is the min", but assuming you want at least two characters other than spaces this will return TRUE if it meets that criterion and FALSE if not.

=AND(ISTEXT(A1),IF(ISNUMBER(SEARCH(" ",A1)),LEN(SUBSTITUTE(A1," ",""))>1),LEN(A1)>1)

 

[Rick Rothstein]

I am looking for a formula that will allow me to count text less than 2 and exclude blank spacing. So if it is one letter it would be wrong and if its one letter with a space it will still be counted as wrong. 2 Characters is the min. Please help

Your requirement statement is a little fuzzy on details. Are you looking only for letters? What about just a letter and a digit? What about just two digits? What about punctuation marks only (?? for example)? Can you tighten up your description just a little bit more for us?

 

[Newbienew]

@footoo Thank you for the formula, will try it out.
@JoeMo I see your point, apologies, I will attempted your method as well.
@Rick Rothstein You make a very valid point, apologies, I am realizing how descriptive I have to be.
I am only looking for letters only
No numbers or punctuation marks
Just anything less than 2 letters and if a there is 1 letter and a space is what I am looking to capture

Please let me know if this was better as i would like to articulate better as I see me asking for help a lot in my newly redesign position. Thank You

 

[Rick Rothstein]

I am only looking for letters only
No numbers or punctuation marks
Just anything less than 2 letters and if a there is 1 letter and a space is what I am looking to capture

Should we raise an error if a there are, say, 2 letters plus a digit or should the non-letters simply be ignored?

 

[Rick Rothstein]

Should we raise an error if a there are, say, 2 letters plus a digit or should the non-letters simply be ignored?

Assuming the latter, give this formula a try...

=SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("A$1:A"&LEN(A1))),1),"abcdefghijklmnopqrstuvwxyz")))>1

 

[Newbienew]

This is a very indepth formula, it works very well. Is there a way to possible apply this to a conditional format in the near future.
I think this formula would definitly mitigate several errors

Thank you so much,

 

[Rick Rothstein]

This is a very indepth formula, it works very well. Is there a way to possible apply this to a conditional format in the near future.
I think this formula would definitly mitigate several errors

My formula works as a Conditional Formatting formula as is provided cell A1 is the active cell for the selection you are applying the Conditional Formatting to (if not, update the various A1 cells references maintaining the $ sign where shown).

 

[Newbienew]

@Rick Rothstein Thank you so much. You have help me on quite a few of my other posted questions you are phenomenal. Truly wish I was as knowledgeable as you are with Excel. I am thankful for the site and those that lend aid. If you just so happen to come across any of my other posting would enjoy your input as well as how to better explain what I am trying to do. Your questions really got thinking. Thanks again
@JoeMo Thank you for you assistance. I will do better at making my titles more precise or clearer on the subject. I am learning a lot from here and didn't realize that there is more ways to make something with a formula on excel
@footoo I am appreciative of your help looking to use your formula on a person home project of mine