Reply
 
Thread Tools Search this Thread
 
Old 23 November 2007, 04:12   #1
Member
 
Country: UK - England
Boat name: Seashell
Make: Redbay
Length: 7m +
Engine: Inboard diesel
Join Date: Jan 2004
Posts: 5,263
Excel - technical question

I've got a spreadsheet of peoples details, including phone numbers. They are currently in normal format eg 01603 715872 - I need to change them to the international format 441603....Is there an easy way of doing this in excel, or do I need to change each one by hand
__________________

__________________
Tim M is offline   Reply With Quote
Old 23 November 2007, 04:27   #2
Member
 
Country: UK - England
Town: Exmouth, Devon
Length: no boat
Join Date: Oct 2004
Posts: 765
Assuming they are all UK numbers, in a different column put

="44"&mid(XXYY,2,99)
where XX=phone number column and YY=current row. Copy the formula down the new column.

Strips the first digit, takes the rest and puts 44 on the front. It won't add 90 spaces to the end.

If you have spaces in the numbers and need to remove them, that's a different matter.

I recommend you then copy the new column and paste the values only into a new column again.
__________________

__________________
www.allgadgets.co.uk
Tel 01395 227727
MikeCC is offline   Reply With Quote
Old 23 November 2007, 05:28   #3
Member
 
Country: UK - England
Boat name: Seashell
Make: Redbay
Length: 7m +
Engine: Inboard diesel
Join Date: Jan 2004
Posts: 5,263
That works a treat, thanks
__________________
Tim M is offline   Reply With Quote
Old 23 November 2007, 18:46   #4
Member
 
Country: UK - England
Town: Gloucestershire
Boat name: Osprey
Make: Osprey Vipermax
Length: 5m +
Engine: E-tec 300 G2
MMSI: TBC
Join Date: Dec 2006
Posts: 4,013
Quote:
Originally Posted by MikeCC View Post
If you have spaces in the numbers and need to remove them, that's a different matter.
If you need to remove spaces use the replace function and hit the space bar then replace it with nothing. works everytime

Chris
__________________
Chris is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off





Copyright 2002- Social Knowledge, LLC All Rights Reserved.

All times are GMT -5. The time now is 18:07.


Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.