Excel 2007 : Finding cells in a row that Do Not begin with

[Ahms]

Hi there folks,

I Have some big excel files.
What I have been trying to do, is select a column and fine all cells in that colums whose values do not begin with 0 (zero). And when these cells are found, a Zero should be given.

However if that cell is empty as some of them are, then I want to ignore them.

Any ideas?

[JBeaucaire]

In an empty column something like this:

=--AND(A1>"",LEFT(A1,1)="0"))

Copy that down the column and you will get 1 and 0 responses.

[Ahms]

Hi there, that was a cool idea you proposed. It made me realise my question was phrased in the wrong way! However for the way it was phrased you gave the perfect answer!

I shall try to rephrase below:

What the problem I have is, I have columns which contain phone numbers.
When the data was put into excel, all of the cell phone numbers had the zero at the beginning cut off.
The land line phone numbers still kept their zero.
For example 07751226792 would have become 7751226792.
Some rows contain records which do not have any phone number.

I need to find a way of checking all of the cells in column of phone numbers to see if they begin with a zero. If they do not then I want to put the zero in.
Thus turning cell phone numbers like 7751226792 back to the correct 07751226792!

On a side note the suggestion to my incorrectly phrased question kindly posted by JBeaucaire was interesting. I have never used an expression like this in excel. (im a basic user)
But seeing it in action got me thinking of maybe some kind of IF( THEN) expression utilising JBeaucaire's suggestion??

[JBeaucaire]

Maybe something like:

=IF(--AND(A1>"",LEFT(A1,1)="0"), A1, "0" & A1)

[Ahms]

Hey,
Thanks so much.
I have never had to do anything like this with excel, and you helped immensely. I understand what's happening. Its just different to IF, THEN clauses I have some across in my limited experience as I have only ever used them in C, or Java where we textually wrote the IF and THEN.
Legend!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.