Is this decent?

[brookdale]

I have been using this forum now for a few days and I just wanted to thank everyone for their help. I am 17, in my first job, and because I am the only person here able to work with excel equations, I am being asked to do a lot of stuff. But I only took a short excel class in 7th grade! I am learning it all now, but I need solutions fast.

Here is one equation that I have so far:

=if(mid(c2,22,1)="M",mid(c2,22,3),if(mid(c2,22,1)="T",mid(c2,22,3),if(mid(c2,22,1)="W",mid(c2,22,3),if(mid(c2,22,1)="F",mid(c2,22,3),mid(H2,22,3)

I have two columns, C and H. One of them is blank and the other will contain the necessary script at space 22/3 characters. However, which column I need occurs somewhat randomly. So here is my question...

Is there a more efficient way to write this? Really I would like to say

=if(mid(c2,22,1)="any letter",mid(c2,22,3),mid(h2,22,3))

However, I don't know if there is away to notate the part in red. I tried a "*", but it doesn't seem to work. my equation does the job, but it seems to be very inefficient.

Thanks.

[swatsp0p]

OK, let me see if I understand what you are after. You are looking at a long string of text that may or may not contain a letter OR a number in position 22. IF this character is a letter, return that letter and the next two characters in the string. If it is NOT a letter, return the three characters from a different cell (H2). If this is what you are doing, try this:

=IF(ISERROR(MID(C2,22,1)+0),MID(C2,22,3),MID(H2,22,3))

Note: the ISERROR uses +0 to test for a numeric value in your text string. If that returns an error (ISERROR=TRUE as 'T'+0 returns #VALUE!), we know it is an alpha character and the IF statement does the True part of the function. If it does not return an error (FALSE, as '1'+0 returns the digit 1), we know it IS a digit and do the False part of the function.

Does this work for you?


Bruce

[brookdale]

Wow. Thank you so much. This stuff is like genius to me. There is still one problem:

You are looking at a long string of text that may or may not contain a letter OR a number in position 22

Neither the C or the H column will contain a number. One will have a letter at spce 22 and the other will just be blank, with nothing in it. This formula works perfectly when C has a letter value and H is blank. However, when C is blank and H has a letter value, this produces a blank.

If I understand this (letter)+0=true. I think it is also saying that (blank space)+0=true? This would mean the value it returns at position 22 is blank. In other words, I will never get a false. I am not sure, but I think you will easily know how to correct it when you see this post.

Sorry if the question was unclear. Thanks so much for your time, Andrew.

[swatsp0p]

I think maybe we are trying to be to complicated. Again, if I understand correctly, either C2 or H2 has the value to look in to return the 22nd-25th characters. All we need to do is see if C2 is blank and direct our MID function from there, as such:

=IF(ISBLANK(C2),MID(H2,22,3),MID(C2,22,3))

Does this work for you?

Bruce

[brookdale]

You, sir, are a genius! This is so simple I cannot even believe it. I just had no idea that there was something like isblank.

I have to ask one more random question. My boss says no one will be able to follow this in other departments...Once I make these huge columns, is there any way to copy those columns with just their values (no formula) into another blank column. I know that this must sound stupid.

Thank you so much.

[swatsp0p]

Highlight the column with your desired results and Copy (Ctrl+C). Select your desired output range and do a Paste Special... and select Values.

The results of the formulas will be pasted (not the formulas themselves).

Good Luck (now, ask for the rest of the day off with pay!!)

Bruce

[brookdale]

Hmm. Thanks again. In high school I only have periodic uses for excel, such as making a simple data table. I never learned how to do any of this. Of course I can learn very quickly, but not instantly that I can write formulas with values I have never seen before.

I really hate to be so annying, but this is just information that I seem to need asap. I think I will be spending some of my time this summer reading an excel manual!

Your advice once more worked perfectly. Thanks again and I will surely be around.

[swatsp0p]

We at the Forums are always glad to help. Hanging around here, you will learn more by accident than most other places by design.

See you around the Forums.

Cheers!