Help using formulas and trickery PLEASE
 

I have a contacts spreadsheet that contains around 1000 contacts in. It has a
number of headers.Firstname through to Notes. Standard headers for contact
info.

As this is a contacts sheet for individual and organisations, not all of the
Rows are complete. I have some empty firstname\surname, some empty
organisations, empty phone\fax\email rows etc etc.

I want to do a few things with this spreadsheet.

- count how many Rows have value in, IE: not blank (I have managed this
with =COUNTIF(C2:C778,"*") )
- The phone numbers rows, show as 01234 567890, I want them toi have no
space and show as 1 continuous number.
- Some of the phone number fields have PA is jon doe 01234 567890, I want
to move the TEXT only to the Notes field and leave the telephone number in
the tel number row,
- Some of the numbers do not have the 0 at the begining of the number, some
do??????? I want all of them to show 0123456789 and not 123456789
- Some of the post code rows contain the add line 3 data as well. IE:
Nottingham NG1 1GN. I want to move the "Nottingham part back into the right
row, (this is becuase they have put the add line 1 as "C floor, block 2, add
line 2 "1 the street"

I am aware that all of the above are possibel through some formulas and
clever trickery, I just cannot for the life of me remember how to do this.

CAN ANYONE help me please?.

thankyou in advance

J


--
Thanks again for all the help

Help using formulas and trickery PLEASE
 

This will eliminate the 0 and the blanks from the phone numbers:
=SUBSTITUTE(SUBSTITUTE(A1,"0","")," ","")


Regards,
Ryan---


--
RyGuy


"smithjb1" wrote:

I have a contacts spreadsheet that contains around 1000 contacts in. It has a
number of headers.Firstname through to Notes. Standard headers for contact
info.

As this is a contacts sheet for individual and organisations, not all of the
Rows are complete. I have some empty firstname\surname, some empty
organisations, empty phone\fax\email rows etc etc.

I want to do a few things with this spreadsheet.

- count how many Rows have value in, IE: not blank (I have managed this
with =COUNTIF(C2:C778,"*") )
- The phone numbers rows, show as 01234 567890, I want them toi have no
space and show as 1 continuous number.
- Some of the phone number fields have PA is jon doe 01234 567890, I want
to move the TEXT only to the Notes field and leave the telephone number in
the tel number row,
- Some of the numbers do not have the 0 at the begining of the number, some
do??????? I want all of them to show 0123456789 and not 123456789
- Some of the post code rows contain the add line 3 data as well. IE:
Nottingham NG1 1GN. I want to move the "Nottingham part back into the right
row, (this is becuase they have put the add line 1 as "C floor, block 2, add
line 2 "1 the street"

I am aware that all of the above are possibel through some formulas and
clever trickery, I just cannot for the life of me remember how to do this.

CAN ANYONE help me please?.

thankyou in advance

J


--
Thanks again for all the help