"T" function broken?

[salgud]

I've been trying different text functions for a spreadsheet I'm working on.
Came across the "T" function, but can't get it to work. (Isn't this a
terrible name for a function, a single letter?)

If I simply apply it alone, as in =t(B7) in cell C7, it seems to behave as
described. If B7 is alpha, T returns the alpha string. It B7 is a number,
it returns a blank. Ok.

But if I put a LEFT function inside it to use it to determine if the
leftmost character in a string is alpha or numeric, it goes kablooie!

I.e., I put =T(LEFT(B7,1)) in Cell C7. If I put abc in cell B7, it returns
the "a". Great. but if I put 222 in cell B7, it returns a 2! It should be
blank since the 2 is numeric. So why does it return 2?

I was going to wrap an IF statement around all this to determine if a
string starts with a alpha or a numeric character, but this obviously wont'
work. Is this Excel function just broken?

 

[Kyle]

I've been trying different text functions for a spreadsheet I'm working on.
Came across the "T" function, but can't get it to work. (Isn't this a
terrible name for a function, a single letter?)

If I simply apply it alone, as in =t(B7) in cell C7, it seems to behaveas
described. If B7 is alpha, T returns the alpha string. It B7 is a number,
it returns a blank. Ok.

But if I put a LEFT function inside it to use it to determine if the
leftmost character in a string is alpha or numeric, it goes kablooie!

I.e., I put =T(LEFT(B7,1)) in Cell C7. If I put abc in cell B7, it returns
the "a". Great. but if I put 222 in cell B7, it returns a 2! It should be
blank since the 2 is numeric. So why does it return 2?

I was going to wrap an IF statement around all this to determine if a
string starts with a alpha or a numeric character, but this obviously wont'
work. Is this Excel function just broken?

Try this

=LEFT(T(B7),1)

It worked for me.

 

[Howard31]

What exactly are you trying to achieve?

 

[Kyle]

Try this

=LEFT(T(B7),1)

It worked for me.- Hide quoted text -

- Show quoted text -

Ok it didn't work perfect if you put 222 in cell B7 you get nothing
but if you put 2b2 in the cell you get 2. Maybe what you could do is
pyt just the first character of B7 in another cell then evaluate that.

 

[salgud]

What exactly are you trying to achieve?

I want to do a validation test on a cell to see if it has a 7 character ID
no. in it. The first character is always alpha, the remaining 6 are
numeric. I want to do the test on a different cell than the one where the
ID is entered, so they can't enter the name without having already entered
an ID (no real way to test for a "real" ID no, but at least I can test that
it's got the correct no. of numbers and letters). Do you have any
suggestions?

 

[salgud]

Ok it didn't work perfect if you put 222 in cell B7 you get nothing
but if you put 2b2 in the cell you get 2. Maybe what you could do is
pyt just the first character of B7 in another cell then evaluate that.

Thanks for your reply. Exactly the problem.

 

[Ron Rosenfeld]

I've been trying different text functions for a spreadsheet I'm working on.
Came across the "T" function, but can't get it to work. (Isn't this a
terrible name for a function, a single letter?)

If I simply apply it alone, as in =t(B7) in cell C7, it seems to behave as
described. If B7 is alpha, T returns the alpha string. It B7 is a number,
it returns a blank. Ok.

But if I put a LEFT function inside it to use it to determine if the
leftmost character in a string is alpha or numeric, it goes kablooie!

I.e., I put =T(LEFT(B7,1)) in Cell C7. If I put abc in cell B7, it returns
the "a". Great. but if I put 222 in cell B7, it returns a 2! It should be
blank since the 2 is numeric. So why does it return 2?

Actually, the "2" that is returned by the LEFT worksheet function is a text
string. So T, seeing text, returns it as designed.

You can perhaps see the difference by looking at the results of the following:

=t(222)
=t("222")

I was going to wrap an IF statement around all this to determine if a
string starts with a alpha or a numeric character, but this obviously wont'
work.

There are several ways to tell if a string starts with a number or not:

=ISNUMBER(-LEFT(B7,1))

is one way. Again, you have to bear in mind that LEFT returns a text string.
Prepending it with a "-" will result in either a number, or an error.


--ron

 

[Jim Cone]

Re: "to see if it has a 7 character ID no. in it. The first character is always alpha, the remaining 6 are numeric."
'--
=AND(CODE(UPPER(B7))>64,CODE(UPPER(B7))<91,LEN(B7)=7,ISNUMBER(VALUE(RIGHT(B7,6))))
--
Jim Cone
Portland, Oregon USA






"salgud" <[email protected]>
wrote in message
I've been trying different text functions for a spreadsheet I'm working on.
Came across the "T" function, but can't get it to work. (Isn't this a
terrible name for a function, a single letter?)

If I simply apply it alone, as in =t(B7) in cell C7, it seems to behave as
described. If B7 is alpha, T returns the alpha string. It B7 is a number,
it returns a blank. Ok.

But if I put a LEFT function inside it to use it to determine if the
leftmost character in a string is alpha or numeric, it goes kablooie!

I.e., I put =T(LEFT(B7,1)) in Cell C7. If I put abc in cell B7, it returns
the "a". Great. but if I put 222 in cell B7, it returns a 2! It should be
blank since the 2 is numeric. So why does it return 2?

I was going to wrap an IF statement around all this to determine if a
string starts with a alpha or a numeric character, but this obviously wont'
work. Is this Excel function just broken?

 

[Peter T]

I want to do a validation test on a cell to see if it has a 7 character ID
no. in it. The first character is always alpha, the remaining 6 are
numeric. I want to do the test on a different cell than the one where the
ID is entered, so they can't enter the name without having already entered
an ID (no real way to test for a "real" ID no, but at least I can test
that
it's got the correct no. of numbers and letters). Do you have any
suggestions?

=AND(LEN(A1)=7,CODE(UPPER(A1))<>CODE(LOWER(A1)),ISERROR(VALUE(RIGHT(A1,6)))=FALSE)

As written not conclusive but would cater for most inputs. Could do a bit
more but a VBA function would be easier.

Regards,
Peter T

 

[salgud]

Actually, the "2" that is returned by the LEFT worksheet function is a text
string. So T, seeing text, returns it as designed.

You can perhaps see the difference by looking at the results of the following:

=t(222)
=t("222")



There are several ways to tell if a string starts with a number or not:

=ISNUMBER(-LEFT(B7,1))

is one way. Again, you have to bear in mind that LEFT returns a text string.
Prepending it with a "-" will result in either a number, or an error.


--ron

Thanks for the reply. Explains the behavior of the T function. Never heard
of a -Left function before. What other functions can you do that with
(besides RIGHT)?

 

[Ron Rosenfeld]

Thanks for the reply. Explains the behavior of the T function. Never heard
of a -Left function before. What other functions can you do that with
(besides RIGHT)?

It is not a -LEFT function. Rather it is a negation (minus sign) of the value
that the LEFT function returns. (As in =-"2").

It just happens that the arithmetic operators will operate on string
representations of numbers, and the result is a number.
--ron

 

[Kyle]

It is not a -LEFT function. Rather it is a negation (minus sign) of the value
that the LEFT function returns.  (As in =-"2").

It just happens that the arithmetic operators will operate on string
representations of numbers, and the result is a number.
--ron

Here is another way you could validate your code.

=IF(AND(LEN(B7)=7,ISERROR(VALUE(RIGHT(B7,6)))<>TRUE,ISERROR(VALUE(LEFT
(B7,1)))=TRUE),"Correct","Not Correct")