Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
formulas for changing formulas? | Excel Discussion (Misc queries) |