Layered "If" statement.

[brookdale]

I would like to know if someone could tell me how to do this.

I have to check if a cell contains the letter M. If it does, then the
word monday must be inserted into another cell. If it is not M, then it
must check to see if it is a T for tuesday. This must continue, checking
for w, th, and f.

I have been trying something like this, but it doesnt work:

{for, say L3}

=if(H3=M,monday,(if(H3=T,tuesday,____________)))

I skipped the ___________

Help?

 

[William DeLeo]

Not that I'm a guru ... I'm just waiting for somone to help me with my
question.

But, it worked for me the way you were trying it. I expect you have
your syntax messed up (extra parentheses around the IF, missing quotes,
etc.?).

Use the below as an example.

=IF(D2="M","Monday",IF(D2="T","Tuesday",IF(D2="W","Wednesday",IF(D2="R","Thursday","Friday"))))

Good luck!

 

[brookdale]

Wow, thank you so much. You are amazing! Now, I have one more eve
specific question. you have, say, =M. What I would need it to be i
contains the phrase "T M", "T T", "T W", etc... This is part of
larger passage and there is a T and space before each of the M, T, W
Th, or F. So rather than have equal (=), how would I do contains
Thanks again.

I may be out today, but I will be back on Monday. Thanks

 

[William DeLeo]

If the format of the cell containing the letter is always the same, then
you could use the MID function. For example, if the cell contained "T
M", and you are looking at the M to designate Monday, then replace the
D2 with:

MID(D2,3,1)

Note that spaces are counted so this formula is counting from the left
3 characters (T,space,M) and returing 1 character starting with the M
.... i.e. just M.

Look up the RIGHT, LEFT and MID functions. One of those should do the
trick.

 

[William DeLeo]

one more thing ... note that if "Th" is your flag for Thursday, then th
MID formula for the Thursday IF statement would need to return
characters vs 1 (i.e MID(D2,3,2)

 

[brookdale]

I dont understand. How would I edit

=IF(D2="M","Monday",IF(D2="T","Tuesday",IF(D2="W","Wednesday",IF(D2="R","Thursday","Friday"))))

so that the logical test part (the first one being D2="M") can be

D2{contains}"T M"

?

I want to replace D2="M" with D2{contains}"T M", but I just do not know
how to notate this.

Thank you so much though for your time.

 

[brookdale]

Anyone have any ideas at all

 

[Dave Peterson]

I'd create a small table on another worksheet.

Put the abbreviations in column A and the long names in column B.

=vlookup(a1,sheet2!a:b,2,false)

to return the longer name.

 

[brookdale]

Here is another idea that I had. How could I make a function that woul
return the vaule of the 6th space in another cell.

{For, say D3}

IF((space 6 of B3)="M","Monday",IF((space 6 o
B3)="T","Tuesday",IF(_________)

The part in red is what I dont know how to do (as you can see I am ne
to excel. I just skipped the _______ rather than type it all out.

Thanks again for all your help

 

[William DeLeo]

Look up the MID function in the help menu.

To extract character 6 from a series of characters in D2, replace D2
with MID(D2, 6, 1).

e.g.

=IF(MID(D2, 6, 1)="M","Monday",IF(MID(D2, 6,
1)="T","Tuesday",IF(MID(D2, 6, 1)="W","Wednesday",IF(MID(D2, 6,
1)="R","Thursday",IF(MID(D2, 6, 1)="F",""Friday")))))

Again note that this is using "R" for Thursday ... if you have "Th"
taking up spots 6 and 7, then use:

=IF(MID(D2, 6, 1)="M","Monday",IF(MID(D2, 6,
1)="T","Tuesday",IF(MID(D2, 6, 1)="W","Wednesday",IF(MID(D2, 6,
2)="Th","Thursday",IF(MID(D2, 6, 1)="F",""Friday")))))