formula question

[Hugh Stanley]

Sorry for the annoying newbie test posts prior to this. I usually
just lurk but I've got an Excel problem that is bugging me to
distraction. ...this is my second post to the group on this issue, I
waited 24 hours for the first to appear and it never seemed to make
it. Anyways, here goes:

Imagine, if you will, the following 4 grids of text in columns
C1...C4:

AWRT Announces Rehr To Keynote
2006/02/23 ... Television has
http://www.radioink.com/... - 78%
Result found by: MSNBC

Directly to the column to the right you want to construct a formula
that does the following:

If C2 begins with "2006", D2's formula returns to Grid D2 the 10
characters "2006/02/23". If C2 does NOT begin with "2006" it returns
the 10 characters in the Grid immediately above that grid (that is,
C1).

....so in the case shown above, the values for D2..D4 would be:

2006/02/23
2006/02/23
http://www

....I've tried all sorts of formulas with imbedded "=IF functions" but
have not hit on the golden hind as yet.

Any help would be greatly appreciated, this will save me 30 minutes
every day.

All the best,

- Hugh -

 

[Don Guillett]

does this help?
=LEFT(IF(LEFT(C2,4)="2006",C2,C1),10)

 

[Pete_UK]

The formula you need to enter into cell D2 is:

=IF(LEFT(C$2,4)="2006","2006/02/23",C$1)

This can then be copied to cells D3 and D4. A slight variation to this
is:

=IF(LEFT(C$2,4)="2006",LEFT(C$2,8),C$1)

which will return the first 8 characters of C2, rather than the fixed
string you quoted.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, the alternative should be:

=IF(LEFT(C$2,4)="2006",LEFT(C$2,10),LEFT(C$1,10))

Pete

 

[Hugh Stanley]

Actually yes, yes it does! You get the genuine Marvel No Prize <tm>!

I had been thinking that if you use "IF" you had to use another "=" in
front of it. ...You have taught me to fish! Thanks, Don!

 

[Don Guillett]

glad to help. In the future, for archival purposes, try to make the subject
line more meaningful.

 

[Hugh Stanley]

OK, I've been using a formula to detect two items that are in an
adjoing column that are exactly the same...

=IF(C81=C80,"***"," ")


This puts some splats in the column to the right to show "hey, they're
the same."

I'm interested in having splats when I have MORE THAN TWO items that
are exactly the same such as the following:


A
A
A ***
A ***
b
c
d
d
e
e
e ***
f


but these embedded formulas inside formulas are vexing me this
morning.

Any help is appreciated!

Thanks,

- Hugh -

 

[Don Guillett]

Have a look in the help index for COUNTIF and incorporate that. Post back
and let us know how you did.