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.


--
brookdale
------------------------------------------------------------------------
brookdale's Profile: http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382478

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=382478

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.


--
brookdale
------------------------------------------------------------------------
brookdale's Profile: http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382478

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=382478

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.


--
brookdale
------------------------------------------------------------------------
brookdale's Profile: http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382478

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=382478

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.


--
brookdale
------------------------------------------------------------------------
brookdale's Profile: http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382478

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!


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=382478