Jonathan has attended:
Excel Advanced course
Excel Intermediate course
UoL 365 Digital Champions Training course
Combining COUNTIF and LEFT Functions in Conditional Formatting
Hi. Essentially in my Excel sheet I want to create a border under cells in a column that share the same 2 first characters, but I do not know what those characters will be ahead of time and information will be added to the spreadsheet after the fact (so I CANNOT specify phrases).
I have used the COUNTIF function to do this bordering function under Conditional Formatting before and I expect the LEFT function is the solution to counting characters, but I cannot work out how to combine the formulas without knowing the specific information ahead of time.
This is my COUNTIF formula so far, would I add the character specification at the end, so instead of any one cell different it's identifying the initial different characters? =COUNTIF($D$2:$D2,$D2)=1
Any advice?
RE: Combining COUNTIF and LEFT Functions in Conditional Formatti
Hi Jonathan,
Thank you for the forum question.
Try:
=COUNTIF($D$2:$D$10,LEFT(D2,2)&"*")>1
You will need to change the range to the actual range. Here the criteria range is $D$2:$D$10.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Combining COUNTIF and LEFT Functions in Conditional Formatti
Hi Jens,
Thank you very much for your help!
Your solution was just a couple characters shy of solving my problem! Appears I needed another $ in front of the LEFT equation and = instead >.
=COUNTIF($D$2:$D2,LEFT($D2,2)&"*")=1
Apart from some minor bugs that I think are due to my unique data set, this is now resolved.
Thanks again.
