Formula needed

[JUAN]

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

 

[Bob Phillips]

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

 

[Pete McCosh]

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.

 

[David McRitchie]

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

 

[Always Learning]

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.

 

[JUAN]

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)

 

[Bob Phillips]

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)

 

[David McRitchie]

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