mobile phone number editing (no zero before number)

[leecavturbo]

yes simple formatting you might think!
but
in general or text i get 4.47866E+11
in number i get 447865955740
it should be 07865955740
also numbers which show correct have the zero at the beginning missing.

if no simple fix can i make a simple code to remove "44" from any numbers with more than 6 digits starting with "44". then any numbers with more than 6 digits beginning with 7 or 1 add a zero infront (in column x)

tia

 

[Sektor]

Set format as Text.

 

[JD_GFI]

If correct numbers always have 11 digits then you can specify a custom format of "00000000000" (no quotes) to display the lead "0".

 

[leecavturbo]

Set format as Text.

doesn't display correctly

 

[leecavturbo]

If correct numbers always have 11 digits then you can specify a custom format of "00000000000" (no quotes) to display the lead "0".

adds too many to numbers with less than 11 doesnt fix the +44 entries
thx for reply tho x 2

 

[energman58]

Set the format of the column with the raw data in to TEXT

Then this formula will do the job:

=IF(AND(LEN(M1)>6,LEFT(M1,2)="44"),"0"&RIGHT(M1,LEN(M1)-2),M1)

Where M1 is replaced by your first entry in the column

This will produce text with a leading zero in the event that you have "44" at the begining

 

[leecavturbo]

it works energman except where the entries have a zero it removes the zero
like wise numbers without 0 or 44 still have no zero

 

[energman58]

It doesnt for me - the original data has to be formatted as TEXT. You do this by highlighting the data and right clicking and select format and then text (sorry if I am teaching my grandmother to suck eggs here)

Then it works - if it is formatted as a number it wont work! You can format the destination cell as TEXT too but you shouldnt have to unless it has been formatted as something other than General (if it has an existing number format then it will screw up and drop the zero)

Two seconds - I had not realised you had some without either zero or 44 at the begining

 

[leecavturbo]

It doesnt for me - the original data has to be formatted as TEXT. You do this by highlighting the data and right clicking and select format and then text (sorry if I am teaching my grandmother to suck eggs here)

Then it works - if it is formatted as a number it wont work! You can format the destination cell as TEXT too but you shouldnt have to unless it has been formatted as something other than General (if it has an existing number format then it will screw up and drop the zero)

Two seconds - I had not realised you had some without either zero or 44 at the begining

hehe. yes no worries about the eggs
i have it as text. i pasted the formula in the next column and changed the m1 for my column data. the result is some numbers correct (44 removed and zero added) some numbers are 1.22E=09 etc

 

[energman58]

=IF(AND(LEN(M1)>6,LEFT(M1,2)="44"),"0"&RIGHT(M1,LEN(M1)-2),IF(NOT(LEFT(M1)="0"),"0"&M1,M1))

Solves the case if the number starts with something other than 44 or 0

Did that solve it for your 1.234 E=09?

I cant make it do that - sorry sun is now over the yardarm here but I will check tomorrow if you still have the problem