Question description:
This user has given permission to use the problem statement for this
blog.
should this work? IF((LEFT(x7,2)= "14","COPD","NEONATE"))
Solved by Z. Y. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
10/05/2018 - 09:29
Hi
User
10/05/2018 - 09:29
hello
Excelchat Expert
10/05/2018 - 09:29
Welcome to Got IT Pro.
Excelchat Expert
10/05/2018 - 09:29
What is x7 cell?
User
10/05/2018 - 09:29
it's a 4 digit code
Excelchat Expert
10/05/2018 - 09:29
https://www.pornhub.com/view_video.php?viewkey=124963325&pkey=22856711
Excelchat Expert
10/05/2018 - 09:30
sorry
Excelchat Expert
10/05/2018 - 09:30
wrong message.
Excelchat Expert
10/05/2018 - 09:30
ignore please.
Excelchat Expert
10/05/2018 - 09:30
my mistake.
Excelchat Expert
10/05/2018 - 09:30
Give me an example of 4 digit code
User
10/05/2018 - 09:30
4202
Excelchat Expert
10/05/2018 - 09:30
ok
Excelchat Expert
10/05/2018 - 09:31
IF((LEFT(x7,2)= "14","COPD","NEONATE"))
Excelchat Expert
10/05/2018 - 09:31
In this case, left 2 digits are 42
Excelchat Expert
10/05/2018 - 09:31
If it is not 14, then it is NEONATE?
User
10/05/2018 - 09:32
in this case the quoted number should be "42" not "14"
Excelchat Expert
10/05/2018 - 09:32
=if(left(A1,2)="42","COPD","NEONATE")
Excelchat Expert
10/05/2018 - 09:33
Left is a function that results in text.
User
10/05/2018 - 09:33
for some reason that's not working in my worksheet
Excelchat Expert
10/05/2018 - 09:33
So, 42 should have double quotes.
User
10/05/2018 - 09:33
I use these if statements all the time...
Excelchat Expert
10/05/2018 - 09:33
What is the result you are getting?
Excelchat Expert
10/05/2018 - 09:34
Did you checked the format?
Excelchat Expert
10/05/2018 - 09:34
If it is a always digit of 4 digits.
User
10/05/2018 - 09:34
the formula you typed contains an error
Excelchat Expert
10/05/2018 - 09:34
Then, I have an idea.
User
10/05/2018 - 09:34
this is the formula
User
10/05/2018 - 09:34
IF(LEFT(x7,2)= "42","DIABETES",IF((LEFT(x7,2)= "19","HEART FAILURE",IF((LEFT(x7,2)= "14","COPD","NEONATE")))))
Excelchat Expert
10/05/2018 - 09:35
=if(value(left(A1,2))=42,"COPD","NEONATE")
Excelchat Expert
10/05/2018 - 09:35
Try using this one.
Excelchat Expert
10/05/2018 - 09:35
Use value function and remove double quotes.
User
10/05/2018 - 09:36
these are some of the codes
User
10/05/2018 - 09:36
4203
4204
4201
1943
1942
1944
1941
1404
1402
1403
6251
6401
6403
Excelchat Expert
10/05/2018 - 09:36
Ok. They are numbers. So, you can comfortably use the above formula with value function.
User
10/05/2018 - 09:37
what if I am using text... then use the quotes
Excelchat Expert
10/05/2018 - 09:37
Yes, you can do that. But, if it is in number format. it may not give result.
Excelchat Expert
10/05/2018 - 09:38
I tested both in google sheets it works fine.
Excelchat Expert
10/05/2018 - 09:39
I did both the formulas. They work fine
User
10/05/2018 - 09:40
HMM.. it's not working for me. What version of excel are you using
User
10/05/2018 - 09:41
=IF(value(LEFT(X7,2))=42,"DIABETES","NEONATE")) does not work
Excelchat Expert
10/05/2018 - 09:41
I am using 2016.
Excelchat Expert
10/05/2018 - 09:42
I am checking in Excel. I tried both in Google sheets.
Excelchat Expert
10/05/2018 - 09:42
=IF(VALUE(LEFT(A1,2))=42,"DIABETES","NEONATE")
Excelchat Expert
10/05/2018 - 09:42
It is working perfectly.
Excelchat Expert
10/05/2018 - 09:45
.
[Uploaded an Excel file]
Excelchat Expert
10/05/2018 - 09:45
Please check this.
User
10/05/2018 - 09:45
I wrote the same formula and I am getting this message: "The Formula you typed contains an erro"
User
10/05/2018 - 09:45
error
Excelchat Expert
10/05/2018 - 09:46
Can you please download from the preview.
Excelchat Expert
10/05/2018 - 09:46
File / Download as Excel.
Excelchat Expert
10/05/2018 - 09:46
Hope, you are able to see the preview at your end.
User
10/05/2018 - 09:47
is this safe to download?
Excelchat Expert
10/05/2018 - 09:47
Yes. It s.
Excelchat Expert
10/05/2018 - 09:48
I have worked on the uploaded file. I would like to see, why it is not working at your end.
User
10/05/2018 - 09:49
I can't download.. can you paste value into your worksheet?
Excelchat Expert
10/05/2018 - 09:49
I have uploaded file in the chat as well.
Excelchat Expert
10/05/2018 - 09:49
Do you use comma ( , ) or semi colon (;) in formulas?
User
10/05/2018 - 09:50
i am using a comma
Excelchat Expert
10/05/2018 - 09:50
Ok. I have provided 2 formulas.
Excelchat Expert
10/05/2018 - 09:50
Can you share your file.
Excelchat Expert
10/05/2018 - 09:50
I will check it.
User
10/05/2018 - 09:51
does this formula work in excel tables
Excelchat Expert
10/05/2018 - 09:51
If it is excel table, please refer the related cell.
Excelchat Expert
10/05/2018 - 09:52
usually, it will be in the form of {@....]
Excelchat Expert
10/05/2018 - 09:52
[@...]
Excelchat Expert
10/05/2018 - 09:52
Can you please try with a new sample and in a new page?
User
10/05/2018 - 09:53
unfortunately, I can't share my file.. HIPPA regulations
Excelchat Expert
10/05/2018 - 09:53
Ok. Please open a new excel file
Excelchat Expert
10/05/2018 - 09:53
And try the formula as shown in preview file.
User
10/05/2018 - 09:56
I think I have a excel version issue.. Thanks for trying.
Excelchat Expert
10/05/2018 - 09:56
What is your version?
Excelchat Expert
10/05/2018 - 09:57
It works in any version more than 2010.
Excelchat Expert
10/05/2018 - 09:57
You can try mid function as well.
User
10/05/2018 - 09:57
I am using 2010
Excelchat Expert
10/05/2018 - 09:57
=if(mid(A1,1,2)="42","COPD","NEONATE")
Excelchat Expert
10/05/2018 - 09:58
Left will work on 2010 as well. Can you please try the above formula?
User
10/05/2018 - 10:00
the mid worked..?
User
10/05/2018 - 10:00
thank you
Excelchat Expert
10/05/2018 - 10:01
Great. Thank you.
Excelchat Expert
10/05/2018 - 10:01
Visit Got IT Pro.
User
10/05/2018 - 10:01
i must have a formatting issue with my values
Excelchat Expert
10/05/2018 - 10:01
Have good day.
User
10/05/2018 - 10:01
you too
Excelchat Expert
10/05/2018 - 10:01
I believe so. But, Mid is also a string function.
Excelchat Expert
10/05/2018 - 10:02
Looking forward.
Excelchat Expert
10/05/2018 - 10:02
Thank you.
User
10/05/2018 - 10:02
yes, i changed the formatting and the formula worked.. I think it's time to restart my PC
Excelchat Expert
10/05/2018 - 10:02
Ok. Great.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
