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

Thanks
Very Cunfused

Hi Very Confused,

You can use the following formula to determine if the first character in a string is a number or a letter:

Here's how the formula works:

1. LEFT(A1,1) extracts the first character from cell A1.
2. VALUE converts the first character to a number (if it's a number) or returns an error (if it's a letter).
3. ISNUMBER checks if the result from step 2 is a number.
4. IF statement returns "Number" if the result from step 3 is TRUE, and "Letter" if it's FALSE.

You can replace "A1" in the formula with the cell reference that contains the string you want to check.

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


"bactfarmer" wrote:

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

Thanks
Very Cunfused

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

One way, at least for English letters,

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

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


On Jan 30, 1:15 pm, "bactfarmer" wrote:
I need a formula that tells you if the first character in a string is
a number or a letter.

Thanks
Very Cunfused

=IF(ISERR(LEFT(A1,1)*1),"letter","number")


"bactfarmer" wrote:

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

Thanks
Very Cunfused

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


"bactfarmer" wrote:

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

Thanks
Very Cunfused

Thanks All! I got it too work. I'm starting to like Excel!

On Jan 30, 12:41 pm, Elkar wrote:
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



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

Thanks
Very Cunfused- Hide quoted text -- Show quoted text -

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

On Jan 30, 11:41 pm, Elkar wrote:
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



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

Thanks
Very Cunfused- Hide quoted text -- Show quoted text -

"Sunrays17" wrote...
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...
....

Adapt some of the other formulas. Alternatively,

=COUNT(SEARCH(LEFT(s,1),"0123456789ABCDEFGHIJKLMNO PQRSTUVWXYZ"))

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

Could you give some examples of what you mean?

"Sunrays17" wrote:

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

On Jan 30, 11:41 pm, Elkar wrote:
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))=9 7,Â*CODE(LEFT(A1,1))<=122)),"Letter","Other")))

HTH,
Elkar



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

Thanks
Very Cunfused- Hide quoted text -- Show quoted text -

On Jan 31, 12:25 am, Elkar wrote:
Could you give some examples of what you mean?



"Sunrays17" wrote:
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

On Jan 30, 11:41 pm, Elkar wrote:
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))=9 7*,*CODE(LEFT(A1,1))<=122)),"Letter","Other")))

HTH,
Elkar

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

Thanks
Very Cunfused- Hide quoted text -- Show quoted text -- Hide quoted text -

- Show quoted text -

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(LEF T(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

On Jan 31, 12:24 am, "Harlan Grove" wrote:
"Sunrays17" wrote...
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...

...

Adapt some of the other formulas. Alternatively,

=COUNT(SEARCH(LEFT(s,1),"0123456789ABCDEFGHIJKLMNO PQRSTUVWXYZ"))

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

Thanks buddy...

we are close to the required result...

tke care...

You could make a slight modification to Harlan's formula which returned true
for alphanumeric,
=IF(COUNT(SEARCH(LEFT(A1,1),"0123456789ABCDEFGHIJK LMNOPQRSTUVWXYZ")),"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(LE FT(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;5 8;65;91;97;123},{"other";"
numeral";"other";"letter";"other";"letter";"other" }),"")

--
Kevin Vaughn



"Sunrays17" wrote in message
ps.com...
On Jan 31, 12:25 am, Elkar wrote:
Could you give some examples of what you mean?



"Sunrays17" wrote:
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

On Jan 30, 11:41 pm, Elkar wrote:
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

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

Thanks
Very Cunfused- Hide quoted text -- Show quoted text -- Hide quoted
text -

- Show quoted text -

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(LEF T(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