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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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!
==============================================


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 Format>Cells
 
Back
Top