Is this decent?

B

brookdale

I have been using this forum now for a few days and I just wanted t
thank everyone for their help. I am 17, in my first job, and because
am the only person here able to work with excel equations, I am bein
asked to do a lot of stuff. But I only took a short excel class in 7t
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 wil
contain the necessary script at space 22/3 characters. However, whic
column I need occurs somewhat randomly. So here is my question...

Is there a more efficient way to write this? Really I would like t
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.
tried a "*", but it doesn't seem to work. my equation does the job, bu
it seems to be very inefficient.

Thanks
 
S

swatsp0p

OK, let me see if I understand what you are after. You are looking at
long string of text that may or may not contain a letter OR a number i
position 22. IF this character is a letter, return _that_ letter an
the next two characters in the string. If it is NOT a letter, retur
the three characters from a different cell (H2). If this is what yo
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 tex
string. If that returns an error (ISERROR=TRUE as 'T'+0 return
#VALUE!), we know it is an alpha character and the IF statement doe
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 Fals
part of the function.

Does this work for you?

Bruc
 
B

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
Click to expand...

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

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
 
B

brookdale

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

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

Thank you so much
 
S

swatsp0p

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

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

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

Bruc
 
B

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

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!
 
Top