Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
vms
 
Posts: n/a
Default change format for phone numbers automatically

I have a worksheet with phone numbers in a column. Some are set up (xxx)
xxx-xxxx some are xxx-xxx-xxxx they all need to be xxxxxxxxxx
Is there a way to do this without retyping all the wrong ones or deleting
each space or -?
--
"You can have everything in life you want, if you will just help enough
other people get what they want." -- Zig Ziglar

  #2   Report Post  
bj
 
Posts: n/a
Default

select the column with yoiur telephone numbers
use edit find replace and enter - for find and nothing for replace
replace all (Note depending on the version of Excel you have, You might
have to do this slightly differently) make sure you try it on a copy before
you do it on th emaster file.
do also find ( replace nothing
do also Find ) replace nothing

"vms" wrote:

I have a worksheet with phone numbers in a column. Some are set up (xxx)
xxx-xxxx some are xxx-xxx-xxxx they all need to be xxxxxxxxxx
Is there a way to do this without retyping all the wrong ones or deleting
each space or -?
--
"You can have everything in life you want, if you will just help enough
other people get what they want." -- Zig Ziglar

  #3   Report Post  
Gord Dibben
 
Posts: n/a
Default

vms

Manually......editreplace

what: (
with: nothing

replace all

Do this for each character (,),-

Alternative..........Run this macro on your selected range.........

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub


Gord Dibben Excel MVP


On Tue, 14 Jun 2005 12:21:02 -0700, vms wrote:

I have a worksheet with phone numbers in a column. Some are set up (xxx)
xxx-xxxx some are xxx-xxx-xxxx they all need to be xxxxxxxxxx
Is there a way to do this without retyping all the wrong ones or deleting
each space or -?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I automatically calculate YTD numbers by changing a date? MDSistah Excel Worksheet Functions 1 April 29th 05 05:52 PM
why does currency format change to number format? Cassie Excel Discussion (Misc queries) 3 March 18th 05 07:57 PM
numbers in cells are automatically divided by 100. Not a format s. Surveyor Excel Worksheet Functions 1 February 22nd 05 05:30 PM
prevent 4/5 in a cell automatically transfer to a date format kei Excel Discussion (Misc queries) 3 December 9th 04 12:52 PM
How can I change the default 'comma' format. Eg paranthesis inste. Duncan Excel Discussion (Misc queries) 1 December 4th 04 01:51 AM


All times are GMT +1. The time now is 09:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"