Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula needed
Hello,
trying to find a formula. I have the following: COLUMN A TEST-T1 TEST-TI-E3 I would like to put in Column B the word BLANK or something if the part does not contain the ending of -E3, but if it contains the -E3, then put that part there. Here's what should look like COLUMN A COLUMN B TEST-T1 BLANK TEST-TI-E3 TEST-TI-E3 basically if part in Column A doesn't have the -E3, then in B put blank but if a part includes that then put that part Please provide any help. Thank you, Juan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula needed
=IF(LEN(A1)=LEN(SUBSTITUTE(A1,"-E3",""),BLANK,A1)
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "JUAN" wrote in message ... Hello, trying to find a formula. I have the following: COLUMN A TEST-T1 TEST-TI-E3 I would like to put in Column B the word BLANK or something if the part does not contain the ending of -E3, but if it contains the -E3, then put that part there. Here's what should look like COLUMN A COLUMN B TEST-T1 BLANK TEST-TI-E3 TEST-TI-E3 basically if part in Column A doesn't have the -E3, then in B put blank but if a part includes that then put that part Please provide any help. Thank you, Juan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula needed
Juan,
this convoluted formula should do what you want, assuming there are only ever three parts to the entries in column A. =IF(ISERROR(FIND("-",A3,(FIND("-",A3,1)+1))),"",RIGHT (A3,LEN(A3)-(FIND("-",A3,(FIND("-",A3,1)+1))))) Cheers, Pete. -----Original Message----- Hello, trying to find a formula. I have the following: COLUMN A TEST-T1 TEST-TI-E3 I would like to put in Column B the word BLANK or something if the part does not contain the ending of -E3, but if it contains the -E3, then put that part there. Here's what should look like COLUMN A COLUMN B TEST-T1 BLANK TEST-TI-E3 TEST-TI-E3 basically if part in Column A doesn't have the -E3, then in B put blank but if a part includes that then put that part Please provide any help. Thank you, Juan . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula needed
Hi Juan,
in A1: either of these. The first checks for the string "-E3" the second just checks for two hyphens. =IF(RIGHT(TRIM(A1),3)="-E3",A1,"(blank)") =IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))= 2,A1,"(blank)") An alternative would be Conditional Formatting, where you would colorize the item in Column A http://www.mvps.org/dmcritchie/excel/condfmt.htm Select Column A (or the entire sheet with Ctrl+A), with cell A1 as the active cell format, Conditional Formatting formula is: =AND(NOT(ISBLANK($A1)),RIGHT(TRIM($A1),3)<"-E3") But if someone pastes content into the cell they will likely wipe out the conditional formatting for that cell. -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "JUAN" wrote in message ... Hello, trying to find a formula. I have the following: COLUMN A TEST-T1 TEST-TI-E3 I would like to put in Column B the word BLANK or something if the part does not contain the ending of -E3, but if it contains the -E3, then put that part there. Here's what should look like COLUMN A COLUMN B TEST-T1 BLANK TEST-TI-E3 TEST-TI-E3 basically if part in Column A doesn't have the -E3, then in B put blank but if a part includes that then put that part Please provide any help. Thank you, Juan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula needed
Hi There,
I Think Bob has had a hard day. Missed a closing bracket and speach marks around the word "Blank" =IF(LEN(A1)=LEN(SUBSTITUTE(A1,"-E3",""),BLANK,A1) =IF(LEN(A1)=LEN(SUBSTITUTE(A1,"-E3","")),"BLANK",A1) All the Best, steve Wilson. "Bob Phillips" wrote in message ... =IF(LEN(A1)=LEN(SUBSTITUTE(A1,"-E3",""),BLANK,A1) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "JUAN" wrote in message ... Hello, trying to find a formula. I have the following: COLUMN A TEST-T1 TEST-TI-E3 I would like to put in Column B the word BLANK or something if the part does not contain the ending of -E3, but if it contains the -E3, then put that part there. Here's what should look like COLUMN A COLUMN B TEST-T1 BLANK TEST-TI-E3 TEST-TI-E3 basically if part in Column A doesn't have the -E3, then in B put blank but if a part includes that then put that part Please provide any help. Thank you, Juan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula needed
First off want to thank all of you who provided info.
Pete, your formula gives me just the last 3 digits, which is not exactly what I need. BOB couldn't make your formula work. DAVID- your formula worked like a charm. So went with one of yours. I was close, but never thought about the Trim function. So thanks again to all of you. Have a good holiday weekend. Juan -----Original Message----- Hi Juan, in A1: either of these. The first checks for the string "-E3" the second just checks for two hyphens. =IF(RIGHT(TRIM(A1),3)="-E3",A1,"(blank)") =IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))= 2,A1,"(blank)") An alternative would be Conditional Formatting, where you would colorize the item in Column A http://www.mvps.org/dmcritchie/excel/condfmt.htm Select Column A (or the entire sheet with Ctrl+A), with cell A1 as the active cell format, Conditional Formatting formula is: =AND(NOT(ISBLANK($A1)),RIGHT(TRIM($A1),3) <"-E3") But if someone pastes content into the cell they will likely wipe out the conditional formatting for that cell. -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "JUAN" wrote in message ... Hello, trying to find a formula. I have the following: COLUMN A TEST-T1 TEST-TI-E3 I would like to put in Column B the word BLANK or something if the part does not contain the ending of - E3, but if it contains the -E3, then put that part there. Here's what should look like COLUMN A COLUMN B TEST-T1 BLANK TEST-TI-E3 TEST-TI-E3 basically if part in Column A doesn't have the -E3, then in B put blank but if a part includes that then put that part Please provide any help. Thank you, Juan . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula needed
Explains the OP's problem.
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Always Learning" <NoMoreSpam@MyEmail wrote in message ... Hi There, I Think Bob has had a hard day. Missed a closing bracket and speach marks around the word "Blank" =IF(LEN(A1)=LEN(SUBSTITUTE(A1,"-E3",""),BLANK,A1) =IF(LEN(A1)=LEN(SUBSTITUTE(A1,"-E3","")),"BLANK",A1) All the Best, steve Wilson. "Bob Phillips" wrote in message ... =IF(LEN(A1)=LEN(SUBSTITUTE(A1,"-E3",""),BLANK,A1) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "JUAN" wrote in message ... Hello, trying to find a formula. I have the following: COLUMN A TEST-T1 TEST-TI-E3 I would like to put in Column B the word BLANK or something if the part does not contain the ending of -E3, but if it contains the -E3, then put that part there. Here's what should look like COLUMN A COLUMN B TEST-T1 BLANK TEST-TI-E3 TEST-TI-E3 basically if part in Column A doesn't have the -E3, then in B put blank but if a part includes that then put that part Please provide any help. Thank you, Juan |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula needed
Hi Juan,
Good, didn't know which you really needed so gave a choice. The initial reason I included the TRIM is because I copied from the posting, but you never know what someone is actually going to type. I could have just run my trimall macro. To Trim your data in place you can use the TRIMALL macro at http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "JUAN" wrote ... DAVID- your formula worked like a charm. So went with one of yours. I was close, but never thought about the Trim function. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula needed | Excel Worksheet Functions | |||
Formula Help Needed! | Excel Discussion (Misc queries) | |||
GP Formula Needed | Excel Discussion (Misc queries) | |||
Excel formula to copy/paste formula needed please. | Excel Discussion (Misc queries) | |||
Formula help needed | Excel Worksheet Functions |