Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# simple formula but cannot get it work.

Posted on 2016-10-20
Medium Priority
51 Views
the following is example of the data i have

i want to do a logical test to check whether the last 5 chars of cell string is numeric or text.

i do this =IF(ISTEXT(RIGHT(A2,5)+0),"Text","Numeric")  but it does not work.

Column A
564654/AJJDNFD
8789788/5654554
0
Question by:excelismagic
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions

LVL 27

Accepted Solution

ProfessorJimJam earned 1004 total points
ID: 41851908
=IF(NOT(ISNUMBER(--RIGHT(A2,5))),"Text","Numeric")
0

LVL 52

Assisted Solution

Rgonzo1971 earned 332 total points
ID: 41851913
Hi,

pls try
``````=IF(ISNUMBER(RIGHT(A2,5)+0),"Numeric","Text")
``````
Regards
0

LVL 32

Assisted Solution

Subodh Tiwari (Neeraj) earned 332 total points
ID: 41851919
Another one....
``````=IF(ISERROR(RIGHT(A2,5)+0),"Text","Numeric")
``````
0

LVL 25

Assisted Solution

Brian B earned 332 total points
ID: 41852048
The formulae above all seem to work. The issue was your proposed formula didn't allow for the fact that adding a number to text produces a #value error. So applying that to your formula, any time your LEFT()+0 function should have returned text, you got an error, which is not text. Thus ISTEXT() returned FALSE and thus you IF() function returned "numeric".
0

LVL 3

Author Closing Comment

ID: 41852132
thank you all.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month8 days, 11 hours left to enroll

#### 721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.