How to insert a repeating dash in phone numbers without dashes in.
 

Please I've been trying to discover a way to insert a dash automatically in
many phone numbers without dashes already input in a single column in Excel.
The phone numbers are all unique so I can not use Find and Replace method
with the asterisk wildcard. The only consistent factor is the dash would be
inserted after the first 3 digits of each phone number in the column. Thanks
for any help or tips!

Hi

in a column adjacent to your data type
=SUBSTITUTE(C1," ","-")
(where C1 is the first cell of your data)
move the cursor over the bottom right hand corner of the cell, when you see
a + double click - this will fill the formula down the column and should
give you what you're after. Once you're happy with the results, select this
new column and copy it
click on C1 (or the first cell of your original list) and choose edit /
paste special - values
to replace your old list with the new one

Cheers
JulieD

"LemonLiptonTea" wrote in message
...
Please I've been trying to discover a way to insert a dash automatically
in
many phone numbers without dashes already input in a single column in
Excel.
The phone numbers are all unique so I can not use Find and Replace method
with the asterisk wildcard. The only consistent factor is the dash would
be
inserted after the first 3 digits of each phone number in the column.
Thanks
for any help or tips!

You could use a "helper " column with a text formula, then copy that column
to itself, eliminating the formula, and leaving the data behind.

=LEFT(A1,3)&"-"&RIGHT(A1,4)

And drag down to copy as needed.

You could then select this column, right click and choose "Copy", right
click again and choose "PasteSpecial", click "Values", then <OK.
You now have a new column of numbers where you could delete the original if
you wished.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"LemonLiptonTea" wrote in message
...
Please I've been trying to discover a way to insert a dash automatically in
many phone numbers without dashes already input in a single column in Excel.
The phone numbers are all unique so I can not use Find and Replace method
with the asterisk wildcard. The only consistent factor is the dash would be
inserted after the first 3 digits of each phone number in the column. Thanks
for any help or tips!

You can use formatting to display dashes in phone numbers. If these have
been entered as numbers (no hyphens, no brackets):

Select the cells with phone numbers
Choose FormatCells
On the Number tab, select the Special category
Choose Phone Number, click OK

LemonLiptonTea wrote:
Please I've been trying to discover a way to insert a dash automatically in
many phone numbers without dashes already input in a single column in Excel.
The phone numbers are all unique so I can not use Find and Replace method
with the asterisk wildcard. The only consistent factor is the dash would be
inserted after the first 3 digits of each phone number in the column. Thanks
for any help or tips!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html