×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

CHARACTER REFERENCING
7

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.

RE: CHARACTER REFERENCING

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", )

RE: CHARACTER REFERENCING

(OP)
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

RE: CHARACTER REFERENCING

(OP)
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?

RE: CHARACTER REFERENCING

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

RE: CHARACTER REFERENCING

(OP)
THANKS A WHOLE BUNCH

RE: CHARACTER REFERENCING

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

RE: CHARACTER REFERENCING

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

RE: CHARACTER REFERENCING

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

CODE

  |  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

RE: CHARACTER REFERENCING

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)))

RE: CHARACTER REFERENCING

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]))

RE: CHARACTER REFERENCING

Handleman,

Thanks!

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

RE: CHARACTER REFERENCING

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

RE: CHARACTER REFERENCING

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

RE: CHARACTER REFERENCING

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

RE: CHARACTER REFERENCING

(OP)
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?
 

RE: CHARACTER REFERENCING

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

RE: CHARACTER REFERENCING

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: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: CHARACTER REFERENCING

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

RE: CHARACTER REFERENCING

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

RE: CHARACTER REFERENCING

(OP)
Hey Joerd, thats correct, so if False, I want it to look at the original formula
(=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


Quote:


Hey Joerd, thats correct, so if False, I want it to look at the original formula
(=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.
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

(OP)
Hi Yogia,

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

Quote:


Hi Yogia,

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 ....
Hi Harley:

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

(OP)
Hi Yogia,

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

Hi Harley:

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

(OP)
Hi Yogi,

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

=IF(OR(C7={"A","D","K","S"}),"?",IF(C7="AD","-",""))&IF(ISNUMBER(MID(E7,2,1)*1),"","N")
or you may want to copy the formula in this post and see if this works for you.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com

RE: CHARACTER REFERENCING

(OP)
Hi Yogi,

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

Hi Harley:

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

(OP)
Hi Yogi,

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

Hi Harley:

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

(OP)
Ok, Yogi,,, I totally screwed that one up..


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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources