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

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

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
.

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

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

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


.

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

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.