Solved

simple formula but cannot get it work.

Posted on 2016-10-20
5
34 Views
Last Modified: 2016-10-20
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
Comment
Question by:excelismagic
5 Comments
 
LVL 25

Accepted Solution

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

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 83 total points
ID: 41851913
Hi,

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

Open in new window

Regards
0
 
LVL 28

Assisted Solution

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

Open in new window

0
 
LVL 23

Assisted Solution

by:Brian B
Brian B earned 83 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

by:excelismagic
ID: 41852132
thank you all.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now