ISNUMBER problem

[stainless]

I am trying to identify if the right character of a cell
is numeric and populate another cell based on the value

e.g. cell A1 contains the text G3

To me, it appears that the right character is 3.
Therefore, if I set up the following function in cell B1,
I would expect a Y in the B1 cell :

=IF(ISNUMBER(RIGHT(A1,1)),"Y","N")

This is not the case. The IF statement is returning a
FALSE value, and thus, my B1 cell contains N.

As far as I can tell, the RIGHT function is returning the
number 3 in double quotes, and I think that these quotes
are causing the problem (although I may be wrong). How
can I get this to work?

 

[Bob Phillips]

See response in .misc


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ken Wright]

This is because the RIGHT function returns a TEXT answer. One way round is to
simply put in a double unary in the formula, that will coerce any really
numerical data to something that ISNUMBER will recognise:-

=IF(ISNUMBER(--RIGHT(A1,1)),"Y","N")

 

[Frank Kabel]

Hi
see your responses in Excel.misc

 

[Norman Harker]

Hi Stainless!

Nearly there!

Use:
=IF(ISNUMBER(--RIGHT(A1,1)),"Y","N")

That -- coerces the text return of RIGHT to be a number if possible.
If A1 is (say) G3 then, -- * text of 3 makes it return the number 3.
But if A1 is (say) GF then, -- * text of F leaves it as F.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Ron Rosenfeld]

That -- coerces the text return of RIGHT to be a number if possible.

Norman,

You only need a single unary in this instance.


--ron

 

[Norman Harker]

Hi Ron!

Thanks. Habit! I'm only just getting rid of the habit of using the
double unary when it isn't required.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Mark]

Thanks for your help everyone. Its getting me there although as I
solve one problem I create another. This is ultimately an attempt to
sort an excel spreadsheet where one of the columns can be blank,
numeric, alphanumeric and numericalpha e.g.

<blank>
<blank>
A1
G1
G4
1
1a
2
5
20
20a
20b
21
21a
999
999b

This is in fact the order I would want (blanks first, alpha numerics
sorted next, numerics and numeric alphas following with 1a in between
1 and 2, and 20b following 20a).

I have been using ISNUMBER and ISTEXT to try and split off details
into other sort columns. If anybody is genius enough to take this
list, mix it up and have some kind of function that will resort it, I
will be grateful.

 

[Ken Wright]

1 helper column

With your data in Col A, header in A1, data starting A2, in B1 put a header and
then in B2 put

=ISNUMBER(-LEFT(A2))&IF(A2="","",TEXT(A2,"@"))

Copy down as far as necessary, select all data and sort in Ascending order on
Col B

 

[Ken Wright]

Ignore this one - See Bob and Auk's response in the other thread.

 

[JE McGimpsey]

This can certainly not the most efficient, but it works. It assumes that
you have no more than one letter following a number:

B1: =IF(A1<>"",IF(ISERR(-LEFT(A1,1)), CHAR(33) & A1,
LEFT(RIGHT(REPT("0",9) & A1, 10+ISERR(-RIGHT(A1,1))) &
REPT(CHAR(96),10),20)),"")

Copy down as far as necessary and sort on column B.

 

[Dave Wilkinson]

Possible alternative root rather than "--" would be to convert th
returned string alpha to a numeric using Value
=IF(ISNUMBER(value(RIGHT(A1,1))),"Y","N"