CHARACTER REFERENCING 7

[Harley78]

I have been beating my head and cannot seem to figure it out, hopefully someone with more smarts than I can help me.

if I have a cell that contains characters,

e.g. Cell A10 contains OA/01 and now I am in Cell D4,(That already has a formula assign) I want to have this cell note that the second character (in this case "A")is Alpha and therefore, place a N in Cell D4.

IS THIS POSSIBLE? OR am I asking to much from Excel?

Thanks for any input or suggestions.

 

[magoo2]

Are you familiar with text functions?

You can use
=mid(A10, 2, 1)
that will give you A

then you can do an IF statement to put the N in the 4th column.

combining, you get something like
=if(mid(A10,2,1) = "A", "N", )

 

[Harley78]

Thanks so much,,, I will give it a shot.... now I already have 5 if statements in that cell... So I believe I just might be at the max....

I'll let you know how it works out..

Many thanks

 

[Harley78]

It work beautiful... however, how is it that if it is a B all the way through z.... do I need to do this 26 times? or can a range be done?

e.g. OA/01 answered...... what if OM/01?

 

[Clyde38]

If the character is always to the left of the "/" you can use =MID(A10,FIND("/",A10)-1,1).
The FIND statement locates the "/" and the MID statement extracts one character to the left (-1).

 

[Harley78]

THANKS A WHOLE BUNCH

 

[handleman]

If I read this correctly, you want to know whether the second character of cell "A10" is a letter or a number, correct? The formula to determine this would be:

=ISNUMBER(VALUE(MID(A10,2,1)))

This will return "TRUE" if the second character is a number and "FALSE" if it's not.

 

[Clyde38]

handleman:
That is correct. If the character has been entered as text (format) =ISNUMBER() will not distinguish. Harley78 could try something like

=IF(AND(CODE(MID(A10,FIND("/",A10)-1,1))>=48,CODE(MID(A10,FIND("/",A10)-1,1))<=57),"Number","Text or other Character")

which will return whatever you want in place of "Number" or "Text or other Character".

 

[yogia]

Hi Harley:

Regarding extracting the second character in a string and determining whether the second character in the string is a numeric digit ... let us look at ...

  |  A             B                          C
--------------------------------------------------------
  | 	  whatIsSecondCharacter	isSecondCharacterAnumber
2 | OA/01	      A	                    FALSE
3 | B5*gp	      5	                    TRUE
4 | C?/24	      ?	                    FALSE

formula in cell B2 is ... =MID(A2,2,1)

formula in cell C2 is ... =ISNUMBER(--(MID(A2,2,1)))

the formulas in B2 and C2 are copied down.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[Clyde38]

Yogia,
Thanks for the "--" tip. Is this documented in Excel?
A B C D
2 OA/01           A FALSE FALSE
3 B5*gp           5 TRUE FALSE
4 C?/24           ? FALSE FALSE



Column C is =ISNUMBER((--MID(A2,2,1)))
Column D is =ISNUMBER((MID(A2,2,1)))

 

[handleman]

Clyde,

The "--" is doing the same thing as the VALUE function in my post. VALUE() returns the numeric value of a text string. Yogia's "--" is forcing Excel to evaluate the character as a number by negating it twice. Essentially, -(-([numeric character stored as text]))

 

[Clyde38]

Handleman,

Thanks!

Whoever ceases to be a student has never been a student.
--George Iles

 

[yogia]

Hi Clyde:

To add to what handleman has kindly described, -- the use of double negation coerces Logical TRUE and FALSE to 1 and 0; some of the other ways to coerce Logical TRUE and FALSE to 1 and 0 respectively are by using

+0
*1
/1

but use of -- appears to be computationally most efficient.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[Clyde38]

Hi Yogi,

Thanks for taking the time to explain.

Only the curious will learn and only the resolute overcome the obstacles to learning. The quest quotient has always excited me more than the intelligence quotient.
Eugene S. Wilson


Clyde

 

[Harley78]

Ok, actually this is what I am trying to achieve,

Say if cell B2 reflects #A/## ( Note that Second Character is Alpha). This cell could be 01/02 or 02/03 and so on as well. But I am looking that if the second character is alpha (A, B, C, D,...through Z. that cell B7 (for example) will note that it is an alpha character and place a N in cell B7.

Hope I explained it better.

Thanks for all your input and help.....

 

[Harley78]

Here is additional information....

N7 I put in a formula (=IF(C7="D", "?", IF(C7="A", "?", IF(C7="K", "?", IF(C7="S", "?", IF(C7="AD", "-"))))), now you know that N7 Changes, However, I also want to add to same cell... so if E7 shows 0A/01 (#A/##)( Note that Second Character is Alpha). This cell could be 01/02 or 02/03 and so on as well. But I am looking that if the second character is alpha (A, B, C, D,...through Z. that cell N7 (for example) will note that it is an alpha character and place a N in cell N7.

Am I making this to confusing?

 

[handleman]

So, if C7 contains "K" and E7 contains "2E/56" you want N7 to contain "?N". Is this correct?

 

[joerd]

And what if C7 doesn't contain D, A, K, S or AD? With the formula you have, you will get FALSE in N7.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Harley78]

wish I could put a picture so you can see the formula in the fields...

Joerd, you are correct,,, however, there is a formula

(=IF(C7="D", "?", IF(C7="A", "?", IF(C7="K", "?", IF(C7="S", "?", IF(C7="AD", "-")))))

already in the field... I just want to add to it so when it also looks at E7 and see's that the second character is alpha, it will fill the cell with an "N" (IT SEEMS TO BE A TWO FOLD FORMULA)

Hope It is better explained.

 

[handleman]

Just make the two formulas and put an "&" between them.