Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Functions
I need a function that will pull a character value from a specified cell and
string location so I can put it into an if statement to return a text string. i.e. Say I have this text string, AP10-FG1A1P, and I wanted to pull the value of the 8th character. What do I use? Find and Search are not returning the value, but only the position. Right and Left return too many characters. Although they are good for the first and last character value when needed. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Functions
Have you tried Mid, as in:
=mid(1,8,1) Regards, Fred. "Charlie" wrote in message ... I need a function that will pull a character value from a specified cell and string location so I can put it into an if statement to return a text string. i.e. Say I have this text string, AP10-FG1A1P, and I wanted to pull the value of the 8th character. What do I use? Find and Search are not returning the value, but only the position. Right and Left return too many characters. Although they are good for the first and last character value when needed. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Functions
try this
=MID(A1,8,1) Mike "Charlie" wrote: I need a function that will pull a character value from a specified cell and string location so I can put it into an if statement to return a text string. i.e. Say I have this text string, AP10-FG1A1P, and I wanted to pull the value of the 8th character. What do I use? Find and Search are not returning the value, but only the position. Right and Left return too many characters. Although they are good for the first and last character value when needed. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Functions
Do you consider the 8th character to be the "1", counting the dash:
=Right(Left(a1,8)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Charlie" wrote in message ... I need a function that will pull a character value from a specified cell and string location so I can put it into an if statement to return a text string. i.e. Say I have this text string, AP10-FG1A1P, and I wanted to pull the value of the 8th character. What do I use? Find and Search are not returning the value, but only the position. Right and Left return too many characters. Although they are good for the first and last character value when needed. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Functions
Perfect Guys, Thanks!
"Mike H" wrote: try this =MID(A1,8,1) Mike "Charlie" wrote: I need a function that will pull a character value from a specified cell and string location so I can put it into an if statement to return a text string. i.e. Say I have this text string, AP10-FG1A1P, and I wanted to pull the value of the 8th character. What do I use? Find and Search are not returning the value, but only the position. Right and Left return too many characters. Although they are good for the first and last character value when needed. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Functions
Now that I am able to pull the value of the character out of the string. I
need to use an IF statement to have TRUE/FALSE text returned. =IF(MID(M3,8,2)=1,"1/8 NPT","1/4 NPT") This returns a False value, would you know why? M3 = AP10-FG1A1P "Mike H" wrote: try this =MID(A1,8,1) Mike "Charlie" wrote: I need a function that will pull a character value from a specified cell and string location so I can put it into an if statement to return a text string. i.e. Say I have this text string, AP10-FG1A1P, and I wanted to pull the value of the 8th character. What do I use? Find and Search are not returning the value, but only the position. Right and Left return too many characters. Although they are good for the first and last character value when needed. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Functions
Two reasons:
*FIRST* Your formula is wrong! It's returning "1A". You should use: =MID(M3,8,1) OR =Right(Left(M3,8)) *SECOND* Text functions return TEXT, *not* true numbers. =Right(Left(M3,8)) AND =MID(M3,8,1) Will return "1" *not* 1. So, you *either* make the text formula return a true number: =--Right(Left(M3,8)) =IF(--Right(Left(M3,8))=1,"1/8 NPT","1/4 NPT") AND =--MID(M3,8,1) =IF(--MID(M3,8,1)=1,"1/8 NPT","1/4 NPT") OR You revise your IF() formula to look for a TEXT "1": =IF(MID(M3,8,1)="1","1/8 NPT","1/4 NPT") =IF(Right(Left(M3,8))="1","1/8 NPT","1/4 NPT") -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Charlie" wrote in message ... Now that I am able to pull the value of the character out of the string. I need to use an IF statement to have TRUE/FALSE text returned. =IF(MID(M3,8,2)=1,"1/8 NPT","1/4 NPT") This returns a False value, would you know why? M3 = AP10-FG1A1P "Mike H" wrote: try this =MID(A1,8,1) Mike "Charlie" wrote: I need a function that will pull a character value from a specified cell and string location so I can put it into an if statement to return a text string. i.e. Say I have this text string, AP10-FG1A1P, and I wanted to pull the value of the 8th character. What do I use? Find and Search are not returning the value, but only the position. Right and Left return too many characters. Although they are good for the first and last character value when needed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple functions, conditional functions | Excel Worksheet Functions | |||
How to convert cell formula functions to code functions | Excel Discussion (Misc queries) | |||
formula/functions for average and if functions | Excel Worksheet Functions | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Nesting functions in the functions dialog box | Excel Worksheet Functions |