CountIF Function Help

[lwilt]

I'm having trouble with the countif function. In my spreadsheet in column C starting cell C2 I have text numbers. If the cell starts with an 8 I want to put a leading 0 in front of it otherwise I want to keep the cell the way it is.


Also, if possible for the cells with just a 0 in them can I turn have it write those as null cells where the function is running?

thank you

 

[lwilt]

Also posted at:

CountIF Function Help
CountIF function Help | Chandoo.org Excel Forums - Become Awesome in Excel

 

[AlKey]

Can you give us few examples of your data and what would you like it to look like?

 

[lwilt]

Joe4 - I did post links...they are above in the 2nd post. The site is running a bit slow this morning for me and it took me a few mins to post them after the orig post loaded since I needed all links for each site.

Alkey...the links to the other site I upload a sample doc. I 'll paste it below for you to see.

[TABLE="width: 301"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Key[/TD]
[TD]REF POINT[/TD]
[TD][/TD]
[TD]FORMULA[/TD]
[TD][/TD]
[TD]Look like[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11122[/TD]
[TD]11122[/TD]
[TD][/TD]
[TD][/TD]
[TD]11122[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]65644444[/TD]
[TD]65644444[/TD]
[TD][/TD]
[TD][/TD]
[TD]65644444[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]811111[/TD]
[TD]811111[/TD]
[TD][/TD]
[TD][/TD]
[TD]0811111[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2121212[/TD]
[TD]2121212[/TD]
[TD][/TD]
[TD][/TD]
[TD]2121212[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11111[/TD]
[TD]11111[/TD]
[TD][/TD]
[TD][/TD]
[TD]11111[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]13111[/TD]
[TD]13111[/TD]
[TD][/TD]
[TD][/TD]
[TD]13111[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]15166[/TD]
[TD]15166[/TD]
[TD][/TD]
[TD][/TD]
[TD]15166[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1211111[/TD]
[TD]1211111[/TD]
[TD][/TD]
[TD][/TD]
[TD]1211111[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]447777[/TD]
[TD]447777[/TD]
[TD][/TD]
[TD][/TD]
[TD]447777[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4545454[/TD]
[TD]4545454[/TD]
[TD][/TD]
[TD][/TD]
[TD]4545454[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]29999999[/TD]
[TD]29999999[/TD]
[TD][/TD]
[TD][/TD]
[TD]29999999[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]899666[/TD]
[TD]899666[/TD]
[TD][/TD]
[TD][/TD]
[TD]0899666[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2222221[/TD]
[TD]2222221[/TD]
[TD][/TD]
[TD][/TD]
[TD]2222221[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1111117[/TD]
[TD]1111117[/TD]
[TD][/TD]
[TD][/TD]
[TD]1111117[/TD]
[/TR]
</tbody>[/TABLE]

 

[lwilt]

Solved!!!

here was the solution
=IF(OR(C2={"","0"}),"",IF(LEFT(C2,1)="8","0"&C2,""&C2))

 

[AlKey]

Try this

[B]=IF(LEFT(C2)="8",0&C2,C2)&""[/B]