To check whether a cell only contains a-z or 1-9 characters?

[x-amount]

Is there a formula to show (true or false) whether a cell contains any characters that aren't a letter or number without resorting to vba?

Cheers,

x

 

[Eyale]

Hi there!

This is brilliant! I appreciate it!

However, I tried to implement its concept with a slight variation and it did not work.

How can you scan only the first few characters in the cell rather than all the characters? I tried replacing Lens with LEFT and it did not work, as follows:

=(SUMPRODUCT(--(MOD(MATCH(CODE(MID('PL & BS Leadsheets'!$A5,ROW(INDEX('PL & BS Leadsheets'!A:A,1):INDEX('PL & BS Leadsheets'!A:A,LEFT('PL & BS Leadsheets'!A5,3))),1)),{0,48,58,65,91,97,123}),2)=0)))=LEFT('PL & BS Leadsheets'!A5,3)

I only want to check if the first letter in the cell IS an a-z or A-Z?? If so (i.e true), I will tie it into a Vlookup to return that cell.

 

[Eyale]

Sorry typo:

=(SUMPRODUCT(--(MOD(MATCH(CODE(MID('PL & BS Leadsheets'!$A5,ROW(INDEX('PL & BS Leadsheets'!A:A,1):INDEX('PL & BS Leadsheets'!A:A,LEFT('PL & BS Leadsheets'!A5,1))),1)),{0,48,58,65,91,97,123}),2)=0)))=LEFT('PL & BS Leadsheets'!A5,1)

 

[jtakw]

Hi,

If you Only want to check the First character of a cell for a-z or A-Z, this is much simpler:

Book1
	A	B	C
3	bob		TRUE
4	345		FALSE
5	23bob		FALSE
6	jack678		TRUE
Sheet493

Cell Formulas
Range	Formula
C3	=AND(CODE(UPPER(A3))>64,CODE(UPPER(A3))<91)

 

[Eyale]

Hi!!

Thank you very much for that. Which part of that code only checks for the first character though? Does the CODE function by default only check the first character?

 

[jtakw]

Yes, that's right.

Just like if you use the LEFT function like LEFT(A1), without specifying the [num_chars], it defaults to 1, but with CODE, it Only checks the 1st character by default.

 

[Eyale]

Oh nice! Thanks for that.

I have an extension to what I am trying to do in case you are that proficient and do not mind giving it a crack.

So finding the first character with a letter works fine; I used it in this way:

=IF(AND(CODE(UPPER('PL & BS Leadsheets'!A5))>64,CODE(UPPER('PL & BS Leadsheets'!A5))<91),'PL & BS Leadsheets'!A5,0)

So pretty much to return the value in that cell. However, when I go on to check the next cell if it is false then it returns 0 and moves on. BUT that is a problem because I will end up having more 0's than actual items with the actual cells returned (i.e. those starting with a letter)

I believe I have to LOOP though and if it is true than returns that cell value, otherwise skips and checks next cell (i.e. 'PL & BS Leadsheets'!A6) and so on to 'PL & BS Leadsheets'!A7 etc. Rather than leaving me with a 0 and taking up rows unnecessarily. Is that possible? And vba might be the way right? Which is fine if you know how to do it.

 

[Fazza]

just a slight change to jtakw's formula : =ABS(CODE(UPPER(A3))-77.5)<13

that was to answer your question about the first letter

the newest question is a bit different - suggest you'd best start a new thread & also to explain what is being done.
when the fuller picture of the requirement is known there might be a totally different (& better) approach available.
And a new question will get more attention (newer readers) too

 

[Fazza]

another way,

=LOOKUP(CODE(UPPER(A3)),{0,65,91},{FALSE,TRUE,FALSE})

 

[pgc01]

To check if the first charater of a cell is a letter, I'd use

=AND(A1>="a",LEFT(A1)<="z")

easy to read

 

[Fazza]

I like that, pgc

However it also returns true for all these characters,
[TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]ƒŠŒ™šœŸªºÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûüýþÿ[/TD]
[/TR]
</tbody>[/TABLE]