validation of a cell with specific character at a specific position
I have a table containing data in two columns. one column contains data in each cell and each cell contains say 10 character long string e.g. AAPD2222A. I want to check that the string in each cell ends with an alpha. please suggest a way out.
Last edited by xlusr; 09-06-2011 at 10:35 AM.
Try this
This will return FALSE if it is a number TRUE if an alpha character.=ISERROR(RIGHT(A2,1)*1)
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
Thanks for the reply.
But, there are some data which ends with special characters like *, ., # etc. If we use your formula for such data it returns FALSE. I want to find out all such cells where the data is ending other than numerical or special characters.
Please advise.
Try this
This will return TRUE if the last character is a>z or A>Z=AND(CODE(UPPER(RIGHT(A2,1)))>=65,CODE(UPPER(RIGHT(A2,1)))<=90)
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
Thank you so much, Marcol. My prob resolved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks