Multiple IF Scenario

[Elnak]

Here is my scenario (I am an average user and do not make complicate
formulas):

I am pulling a single digit from a 12 digit code using (MID(B11,4,1))
Example: B11 = XXXAXXXXXXXX, then the digit pulled is "A"

If the 4th digit is 1-9, then nothing needs to be done, but, if the 4t
digit is A, B or C, then A=10, B=11, C=12

As you might have guessed, the 4th digit is a month code, that I nee
to extract and properly display as a number ranging from 1-12

I tried a multiple IF formula that failed to produce a value, but di
not cause errors. I tried AND, OR, THEN, ELSE, but I am not very goo
at this

Any help or sugestions would be greatly appreciate

 

[Frank Kabel]

Hi
try the following
=IF(ISNUMBER(--MID(B11,4,1)),--MID(B11,4,11),CODE(MID(B11,4,1))-65)
Note: This does not check if there're other characters as A,B,C at the
4th position

 

[Elnak]

Pulling the 1-9 numbers is simple and needs nothing but the MID
function. It is the A, B and C that are causing me the troubles, but
thank you for the input

 

[A.W.J. Ales]

Hi Frank,

Sorry. I just noticed it. I'm not controlling your answers but just
"bumped" in to it.

I suppose you want a deduction of 55 in stead of 65.
Also : the characters can only be capital A, B or C. a,b and c are also to
be excluded.

If a, b and c are also possible as 4th character the OP might change to :
=IF(ISNUMBER(--MID(B11,4,1)),--MID(B11,4,11),CHOOSE(MATCH(MID(B11,4,1),{"A",
"B","C"},0),10,11,12))


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Frank Kabel]

Hi
the formula I posted will do that It uses the CODE function for
converting A,B,C to a value. e.g.
CODE("A") = 65
I only made on slight error. Change the function to
=IF(ISNUMBER(--MID(B11,4,1)),--MID(B11,4,11),CODE(MID(B11,4,1))-55)

A ->65 - 55 = 10
B ->66 - 55 = 11
c -> 67 - 55 = 12

 

[Frank Kabel]

Hi Auk
no problem with that
I also just recognized this error. I made the assumption what only
A,B,C would occur b ut you're right, testing for lower case would be a
good addition

 

[Elnak]

Lowercase does not work, but uppercase does. A value of 1-9 causes you
formula to not function the way I need it to, (to return the valu
entered). Here are the possible values and the return values required
1 - 1, 2 - 2, 3 -3, 4 - 4, 5 - 5, 6 - 6, 7 - 7, 8 - 8, 9 - 9, a/A - 10
b/B - 11, c/C - 12

Is it possible without using Macros? I do not know how to progra
Macros. =(

Again, thanks for all the input, it is very much appreciate

 

[Frank Kabel]

Hi
sorry
this morning is my 'typo' morning
Use
=IF(ISNUMBER(--MID(B11,4,1)),--MID(B11,4,1),CODE(MID(B11,4,1))-55)

If you also need lower cases use
=IF(ISNUMBER(--MID(B11,4,1)),--MID(B11,4,1),CODE(UPPER(MID(B11,4,1)))-5
5)

 

[Ron Rosenfeld]

Here is my scenario (I am an average user and do not make complicated
formulas):

I am pulling a single digit from a 12 digit code using (MID(B11,4,1)).
Example: B11 = XXXAXXXXXXXX, then the digit pulled is "A"

If the 4th digit is 1-9, then nothing needs to be done, but, if the 4th
digit is A, B or C, then A=10, B=11, C=12

As you might have guessed, the 4th digit is a month code, that I need
to extract and properly display as a number ranging from 1-12

I tried a multiple IF formula that failed to produce a value, but did
not cause errors. I tried AND, OR, THEN, ELSE, but I am not very good
at this

Any help or sugestions would be greatly appreciated

If you have the analysis toolpak installed, you can use:

=HEX2DEC(MID(A1,4,1))

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.

=======================================

If you don't have/want the ATP installed, then you could use:

=CODE(UPPER(MID(A4,4,1)))-48-7*(CODE(MID(A4,4,1))>57)

The UPPER function may or may not be necessary depending on whether the 4th
character can be a lower case letter.


--ron

 

[Peter Atherton]

Elnak

This assumes that A, B and C are in upper case. If not you
will have to insert The Upper function into each term.

=IF(CODE(MID(B11,4,1))=65,10,IF(CODE(MID(B11,4,1))=66,11,IF
(CODE(MID(B11,4,1))=67,12,VALUE(MID(B11,4,1)))))

Copy this in to a text editor and make sure the formula is
in one line before copying it into Excel.

Regards
Peter

 

[Tom Ogilvy]

If you have the analysis toolpak installed you can use

=HEX2DEC(MID(B11,4,1))

 

[Elnak]

Thanks everyone. I really appreaciate all of the input!

I will look into the toolpack and take all of this info to my hom
machine, as I can not install anything here at work



Thanks agai