Is the first character a number or letter

B

bactfarmer

I need a formula that tells you if the first character in a string is
a number or a letter.

Thanks
Very Cunfused
 
R

Ron Coderre

Try something like this:

for a value in A1

B1: =ISNUMBER(--LEFT(A1,1))
That formula returns TRUE if the 1st char is a number, otherwise FALSE

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
H

Harlan Grove

bactfarmer said:
I need a formula that tells you if the first character in a string is
a number or a letter.
Click to expand...

One way, at least for English letters,

=IF(s<>"",LOOKUP(CODE(s),{0;48;58;65;81;97;123},
{"other";"numeral";"other";"letter";"other";"letter";"other"}),"")
 
K

krcowen

try something like


=ISNUMBER(LEFT(A1,1)*1)

where A1 is the string you are evaluating. Multiplying the leftmost
character by 1 seems to be necessary to keep it from being considered
a text string that looks like a number.

Good luck.

Ken
Norfolk, Va
 
E

Elkar

This is a bit more involved, but this will also tell you if the first
character is something other than a letter or number.

=IF(A1="","",IF(AND(CODE(LEFT(A1,1))>=48,CODE(LEFT(A1,1))<=57),"Number",IF(OR(AND(CODE(LEFT(A1,1))>=65,CODE(LEFT(A1,1))<=90),AND(CODE(LEFT(A1,1))>=97,CODE(LEFT(A1,1))<=122)),"Letter","Other")))

HTH,
Elkar
 
S

Sunrays17

Hi there...

i am looking forward to know is there any formula to know if a value
is alphanumeric too, as well as its a number or a letter...

Take care...
Sunrays17
 
H

Harlan Grove

Sunrays17 said:
i am looking forward to know is there any formula to know if a value
is alphanumeric too, as well as its a number or a letter...
Click to expand...
....

Adapt some of the other formulas. Alternatively,

=COUNT(SEARCH(LEFT(s,1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))

returns TRUE if the first char in s is alphanumeric, FALSE otherwise.
This is easily adapted to non-English languages with more/other
letters.
 
E

Elkar

Could you give some examples of what you mean?

Sunrays17 said:
Hi there...

i am looking forward to know is there any formula to know if a value
is alphanumeric too, as well as its a number or a letter...

Take care...
Sunrays17
Click to expand...
 
S

Sunrays17

Could you give some examples of what you mean?








- Show quoted text -
Click to expand...

Well... exactly don have any examples as i amjut trying it out...
but still...

consider A1 as "A155AbAAn", than giving
"=IF(A1="","",IF(AND(CODE(LEFT(A1,1))>=48,CODE(LEFT(A1,1))<=57),"Number
",IF(­­
OR(AND(CODE(LEFT(A1,1))>=65,CODE(LEFT(A1,1))<=90),AND(CODE(LEFT(A1,1))>
=97­,­CODE(LEFT(A1,1))<=122)),"Letter","Other")))",


the output is either "Letter", or "Number"....

if we can get "Aplhanumeric" tooo....


Regards,
Sunrays17
 
S

Sunrays17

...

Adapt some of the other formulas. Alternatively,

=COUNT(SEARCH(LEFT(s,1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))

returns TRUE if the first char in s is alphanumeric, FALSE otherwise.
This is easily adapted to non-English languages with more/other
letters.
Click to expand...

Thanks buddy...

we are close to the required result...

tke care...
 
K

Kevin Vaughn

You could make a slight modification to Harlan's formula which returned true
for alphanumeric,
=IF(COUNT(SEARCH(LEFT(A1,1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")),"alphan
umeric","not")

BTW, when I tested on my test data, which I created using the formula
=CHAR(ROW())&"123" (in a1:a255,) I got some false positives and not sure
why, and was not able to eliminate the false positives. They weren't the
same false positives as I was getting with my failed attempt using
=IF(OR(AND(LEFT(A1,1)>="a",LEFT(A1,1)<="z"),AND(LEFT(A1,1)>="A",LEFT(A1,1)<=
"Z")),"letter",IF(ISNUMBER(--(LEFT(A1,1))),"numeral","other")) (for the
earlier request. Note, I had read Ron Coderre's reply earlier which is
probably how I came up with the isnumber... part of the formula.)

BTW, on Harlan's earlier formula, it appears 81 should in actuality be 91:
=IF(LEFT(A1,1)<>"",LOOKUP(CODE(LEFT(A1,1)),{0;48;58;65;91;97;123},{"other";"
numeral";"other";"letter";"other";"letter";"other"}),"")

--
Kevin Vaughn



Could you give some examples of what you mean?






=IF(A1="","",IF(AND(CODE(LEFT(A1,1))>=48,CODE(LEFT(A1,1))<=57),"Number",IF(­
text -

- Show quoted text -
Click to expand...

Well... exactly don have any examples as i amjut trying it out...
but still...

consider A1 as "A155AbAAn", than giving
"=IF(A1="","",IF(AND(CODE(LEFT(A1,1))>=48,CODE(LEFT(A1,1))<=57),"Number
",IF(­­
OR(AND(CODE(LEFT(A1,1))>=65,CODE(LEFT(A1,1))<=90),AND(CODE(LEFT(A1,1))>
=97­,­CODE(LEFT(A1,1))<=122)),"Letter","Other")))",


the output is either "Letter", or "Number"....

if we can get "Aplhanumeric" tooo....


Regards,
Sunrays17
 
Top