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?

On Jul 14, 2:23*pm, salgud wrote:
I've been trying different text functions for a spreadsheet I'm working on.

What exactly are you trying to achieve?

--
A. Ch. Eirinberg
"salgud" 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?

On Jul 14, 2:31*pm, Kyle wrote:
On Jul 14, 2:23*pm, salgud wrote:





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?

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.

On Tue, 14 Jul 2009 21:32:46 +0100, Howard31 wrote:

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?

On Tue, 14 Jul 2009 13:35:52 -0700 (PDT), Kyle wrote:

On Jul 14, 2:31*pm, Kyle wrote:
On Jul 14, 2:23*pm, salgud wrote:





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?

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.

Thanks for your reply. Exactly the problem.

On Tue, 14 Jul 2009 14:23:36 -0600, salgud wrote:

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

"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"
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?

"salgud" wrote in message
...
On Tue, 14 Jul 2009 21:32:46 +0100, Howard31 wrote:

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?

=AND(LEN(A1)=7,CODE(UPPER(A1))<CODE(LOWER(A1)),IS ERROR(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

On Tue, 14 Jul 2009 17:34:41 -0400, Ron Rosenfeld wrote:

On Tue, 14 Jul 2009 14:23:36 -0600, salgud wrote:

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

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)?

On Tue, 14 Jul 2009 15:53:29 -0600, salgud wrote:

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

On Jul 14, 7:02*pm, Ron Rosenfeld wrote:
On Tue, 14 Jul 2009 15:53:29 -0600, salgud wrote:
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

Here is another way you could validate your code.

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

On Tue, 14 Jul 2009 14:47:30 -0700, Jim Cone wrote:

"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))))

Thanks to everyone for your helpful replies. Got this one nailed.