smudgecfc

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 adavance

J

Glaswegian

Hi and welcome to TSF.

Some of what you want can be achieved with formulae and formatting - some might need some VBA code (depends if I can remember my formulae...)

To remove spaces in text, use something like

=SUBSTITUTE(B4," ","")

or select your cells, go to Edit > Replace, Find what: Type a space character > Replace All.

To show a leading zero, you need to format the cells, using a custom format - use 0 (zero) as the placeholder. For example, you want 123 to show as 0123. Format the cell using a custom format of '0000'.

For something like 'Nottingham NG1 1GN', you could split it using a LEFT or MID function, perhaps using the space in the text as your 'marker'. This would require a more complex formula or custom function and I would be tempted to use VBA.

Hopefully that should at least get you started.

smudgecfc

Thanks for this. It has helped in some cases.

I have been a bit of an idiot though-as I have noticed this morning, that the numbers ACTUALLY need to be prefixed with 020. 99% of the numbers are London numbers, and as the database originates in london, the owner never prefixed them with the area code. So I need a rule that says.

"IF number starts 7 or 8 then prefix with 020"
some of the numbesr in there are outside london, so do not need amending.

I hope this makes sense.

ShosMeister

Are you trying to reformat the existing data or come up with a method that will continue to work as new data is added assuming that it will not be in the correct format? That may have a bearing on the "solution".

If you are just trying to change the data so that it is "fixed", then you can do some creative Find/Replace options as well. Like removing the space from the phone number, you can select just that column and replace the spaces with nothing.

As for the area code, you may want to check the length of the existing data and if the length doesn't appear to have an area code, then add the 020 as opposed to looking just at the first number. For example, here in the states, our phone numbers are all 7 digits. If the phone field were only 7 characters long, then I would add the area code.