LinkBack URL
About LinkBacks
can I create an IF function to do an operation based on the presence of an alphabetic letter in a numeric input?
e.g
an entry of 140 makes the next column show (140 - 100)
but an entry of 140N makes the next column show 140.
can I create an IF function to do an operation based on the presence of an alphabetic letter in a numeric input?
e.g
an entry of 140 makes the next column show (140 - 100)
but an entry of 140N makes the next column show 140.
Try:
=If(Isnumber(A1+0),A1-100,Left(A1,Len(A1)-1)+0)
This assumes if the item is alphanumeric, then it contains a letter at the end.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Hi,
Welcome to the forum, not exactly sure what you mean, but does this work for you?
=IF(ISNUMBER(A1),A1-100,LEFT(A1,LEN(A1)-1)+0)
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
My thougths were:
=IF(RIGHT(A1,1)="N",LEFT(A1,(LEN(A1)-1)),A1 & " - " & A1-40)
This will allows for multiple nested ifs if he has more then one letter to use.
I think he was shooting for a range if not a number.
Hope this helps,
Dan
Last edited by split_atom18; 06-08-2009 at 03:29 PM. Reason: added 3rd line
"I am not a rocket scientist, I am a nuclear engineer." - Split_atom18
If my advice has been helpful to you, then please help me by clicking on the "Star" and adding to my reputation, Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks