**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.*