General Betting
There is currently 1 person viewing this thread.
I have a contact database in Excel format.
In the phone field is a normal UK phone number. All numbers start with a 0. I need to delete that first number and replace it with 44.
01234567890 will become 441234567890.
It seems pretty simple but I cannot manage it.
Ladies and gentlemen any help would be greatly appreciated.
In the phone field is a normal UK phone number. All numbers start with a 0. I need to delete that first number and replace it with 44.
01234567890 will become 441234567890.
It seems pretty simple but I cannot manage it.
Ladies and gentlemen any help would be greatly appreciated.
sort by:
Show
per page
Replies: 16
|
By:
Ctrl 'f', untick 'entire cell', find '0', replace with '44'.
|
|
By:
Thanks Aviboyd. Great minds think alike. I tried that already. But it replaces every 0 in the number with 44.
So 01234567890 becomes 4412345678944 rather than 441234567890. You see my point? But thanks for taking the trouble to post. |
|
By:
I can't remember how they work exactly but google "left" "Right" and "len" functions
I -think- what you want is something like =right(A1,len(A1)-1) where right returns all the text at the right hand side of A1 (where your number is, as a string) and len makes sure you get all but the first digit. you'd need to make this something like ="44"+right(a1,len(a1)-1) to add the 44 this won't be right first time but should be close i think |
|
By:
storing the numbers as text not numbers should help too i believe
|
|
By:
=SUBSTITUTE(A1,"0","44",1)
|
|
By:
The above edits the first instance of zero. If you have any phone number which doesn't start with zero then it will edit the first zero it finds.
|
|
By:
Thanks Lori and Ian.
Ian, where do I put =SUBSTITUTE(A1,"0","44",1)? Sorry, but I am a real novice at this. |
|
By:
Insert a column to the right of the phone number.
Copy the formula into the first cell of the new column. You will need to change "A1" to whatever cell if to the left. Copy the formula down the column. |
|
By:
Thanks Ian. I get ERR508. Any idea?
|
|
By:
right clicck on the cell click on format cell change to numbers that will do it good luck
|
|
By:
Thanks guys for all your help but I think this is beyond me.
Would any kind soul edit it for me at http://whylose.com/help/1000.xls please? you will be rewarded in heaven. LOL. My email john at whylose dot com. The data is publicly available elsewhere so I am not sharing anything I should not. |
|
By:
Hopefully I will be able to use the edited version as a template?
|
|
By:
Email sent
|
|
By:
You are a diamond Ian. Received. If you have a Paypal account I will pop 20 quid over to you. Have a drink on me. Let me know on email. Thanks again mate.
|
|
By:
I don't want anything for helping you out MOM - donate something to Haiti if you wish.
|
|
By:
Thanks mate. Will do.
|