CHARACTER REFERENCING
CHARACTER REFERENCING
(OP)
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.
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.





RE: CHARACTER REFERENCING
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", )
RE: CHARACTER REFERENCING
I'll let you know how it works out..
Many thanks
RE: CHARACTER REFERENCING
e.g. OA/01 answered...... what if OM/01?
RE: CHARACTER REFERENCING
The FIND statement locates the "/" and the MID statement extracts one character to the left (-1).
RE: CHARACTER REFERENCING
RE: CHARACTER REFERENCING
=ISNUMBER(VALUE(MID(A10,2,1)))
This will return "TRUE" if the second character is a number and "FALSE" if it's not.
RE: CHARACTER REFERENCING
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".
RE: CHARACTER REFERENCING
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 ...
CODE
--------------------------------------------------------
| whatIsSecondCharacter isSecondCharacterAnumber
2 | OA/01 A FALSE
3 | B5*gp 5 TRUE
4 | C?/24 ? FALSE
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
RE: CHARACTER REFERENCING
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)))
RE: CHARACTER REFERENCING
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]))
RE: CHARACTER REFERENCING
Thanks!
Whoever ceases to be a student has never been a student.
--George Iles
RE: CHARACTER REFERENCING
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
RE: CHARACTER REFERENCING
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
RE: CHARACTER REFERENCING
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.....
RE: CHARACTER REFERENCING
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?
RE: CHARACTER REFERENCING
RE: CHARACTER REFERENCING
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: CHARACTER REFERENCING
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.
RE: CHARACTER REFERENCING
RE: CHARACTER REFERENCING
(=IF(C7="D", "?", IF(C7="A", "?", IF(C7="K", "?", IF(C7="S", "?", IF(C7="AD", "-"))))), Only do I want it to be N when the e7 cell when False.
And thanks to all again for your help!!!! You helped tremendously
RE: CHARACTER REFERENCING
Hi Harley:
Here is one way ...
=IF(OR(C7={"A","D","K","S"}),"?",IF(C7="AD","-",""))&IF(ISNUMBER(MID(E7,2,1)*1),"","N")
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: CHARACTER REFERENCING
That looks much better, and it seems that it should work, but it looks like there would be a dash (Hyphen, depending on where your from I suppose, lol), or question mark at the beginning of each. But it is definately what I was looking for,,,,
Thanks for all your help peoples!!!!!!!
RE: CHARACTER REFERENCING
Does it work according to your specifications now? ... if some thing is still missing, please provide a clearer description and then let us take it from there.
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: CHARACTER REFERENCING
It operates exactly how I want it, however, it shows either a ?N or -N, trying now how I can convert those to just plain N
RE: CHARACTER REFERENCING
Let me see if understand what you are saying ... because I may have already taken care of what you are trying to do now.
See the formula ...
=IF(OR(C7={"A","D","K","S"}),"?",IF(C7="AD","-",""))&IF(ISNUMBER(MID(E7,2,1)*1),"","N")
this will put ? if C7 houses A, D, K, or S, and it will put - if C7 houses AD
However I had extended your requirement in the sense that what if cell C7 did not house A, D, K, S, or AD, then it will put nothing in there.
So if cell C7 houses anything other than A, D, K, S, or AD, and the second character in E7, is ALPHA, then the formula will result in simply ...
N
Does it meet your requirement or are you looking for still something else?
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: CHARACTER REFERENCING
I thought it would just show N according to your formula, but mine isn't show ing this result.
It is doing exactly what I want, but my final result is reflecting either a ? then N (?N) or - and N (-N)when it is alpha. Thats what I was trying to reflect earlier, sorry about that..
RE: CHARACTER REFERENCING
Hi Harley:
Please check your formula and make sure it is ...
CODE
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: CHARACTER REFERENCING
I did copy the last one as well as this one... and still the - or ? comes before the N. I opened a new workbook and started fresh and still it does it. So yours doesn't do it? hmmmmmm I'll keep plugging along and check the formating of the cell, but with a new workbook/worksheet, its tougher to figure out why mine is doing it.
again, so many thanks
RE: CHARACTER REFERENCING
Please post what do you have in
1) cell C7
2) cell E7
3) formula cell
You may want to directly copy from your worksheet and post in your response.
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: CHARACTER REFERENCING
Here is what it looks like now..... I listed the formula's at the bottom of the cell. Let me know if you require additional info...
C E H N
DT Rev Mat Disp
Cur / New Type INV
7 A 01/02 HALB ?
8 A 0M/01 HALB ?N
9 AD 01/02 DOKU -
10 AD OM/01 DOKU -N
11 D 01/02 HALB ?
12 D OM/01 HALB ?N
13 K 01/02 HALB ?
14 K 0M/01 HALB ?N
15 S 01/02 KMAT ?
16 S 0M/01 KMAT ?N
H7 = =IF(C7="D", "HALB", IF(C7="A", "HALB", IF
(C7="K", "HALB", IF(C7="S", "KMAT", IF
(C7="AD", "DOKU")))))
N7 = =IF(C7="D", "-", IF(C7="A", "?",
IF(C7="K", "-", IF(C7="S", "-",
IF(C7="AD", "-")))))
RE: CHARACTER REFERENCING
Thanks for posting data in your columns C, E, H, and N. However, I am not following you here ... what are you trying to show me. Where is your formula that results in N?
If you still want to pursue it, please describe clearly ...
1) your source data
2) your expected result(s) and the basis for that result
3) what do you see as the proble? ... what doesn't jibe?
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: CHARACTER REFERENCING
Anyway what my goal is.... Cell N7 should contain N if it is Alpha ( Hence, your formula)
=IF(OR(C7={"A","D","K","S"}),"?",IF(C7="AD","-",""))&IF(ISNUMBER(MID(E7,2,1)*1),"","N")
The N7 formula above is a mistake that is actually cell I7 Sorry for the confusion
RE: CHARACTER REFERENCING
Hi Harley:
Whether that formula is housed in cell I7 or N7 doesn't matter. The result of the formula is based on entries in cell C7 and E7. From what I understood from you, the formula does exactly what you said you want it to do.
So, I suggest, you take your time, check the formula with various entries in cells C7, and E7 and figure out what is happening. And if you still have a problem, post back and then let us take it from there.
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com