ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   New Users to Excel (https://www.excelbanter.com/new-users-excel/)
-   -   Is there a way to insert dashes in a column of numbers (https://www.excelbanter.com/new-users-excel/90179-there-way-insert-dashes-column-numbers.html)

Mickie

Is there a way to insert dashes in a column of numbers
 
Hi!
I have a column of numbers (2100 to be exact) that I need to insert dashes
so they resemble 333-33-3333. The numbers currently show 333333333. Is
there anything I can do?
Thanks so much for any suggestions..

ExcelBanter AI

Answer: Is there a way to insert dashes in a column of numbers
 
How to insert dashes in a column of numbers in Excel:
  1. Select the column of numbers that you want to add dashes to.
  2. Right-click on the selected cells and choose "Format Cells" from the drop-down menu.
  3. In the "Format Cells" dialog box, select the "Custom" category.
  4. In the "Type" field, enter the following format code: 000-00-0000
  5. Click "OK" to apply the formatting to the selected cells.

This format code tells Excel to display the numbers in the format of three digits, followed by a dash, then two digits, another dash, and finally four digits. So, for example, the number 333333333 will now be displayed as 333-33-3333.

Ron Coderre

Is there a way to insert dashes in a column of numbers
 
If you only need the numbers to *appear* as if they contain dashes, try this:

Select the range of numbers

From the Excel main menu:
<format<cells<number tab
Category: Special
Type: Social Security Number

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Mickie" wrote:

Hi!
I have a column of numbers (2100 to be exact) that I need to insert dashes
so they resemble 333-33-3333. The numbers currently show 333333333. Is
there anything I can do?
Thanks so much for any suggestions..


Pete_UK

Is there a way to insert dashes in a column of numbers
 
Alternatively, if you want them to be changed permanently, then you can
enter this formula in B1, assuming your existing numbers start at A1:

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

Copy the formula down for as many entries as you have in column A. You
can fix the values in column B by highlighting all the cells, then
<copy, then Edit | Paste Special | Values (check), OK and <Esc, and
then delete column A as you no longer need it.

Ron's method is a bit quicker!

Hope this helps.

Pete


Mickie

Is there a way to insert dashes in a column of numbers
 
Ron and Pete...you are wonderful...thanks so much!!!
It worked immediately....of course...ha!

"Ron Coderre" wrote:

If you only need the numbers to *appear* as if they contain dashes, try this:

Select the range of numbers

From the Excel main menu:
<format<cells<number tab
Category: Special
Type: Social Security Number

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Mickie" wrote:

Hi!
I have a column of numbers (2100 to be exact) that I need to insert dashes
so they resemble 333-33-3333. The numbers currently show 333333333. Is
there anything I can do?
Thanks so much for any suggestions..


Pete_UK

Is there a way to insert dashes in a column of numbers
 
Thanks for feeding back.

Pete


CLR

Is there a way to insert dashes in a column of numbers
 
Another alternative would be.........

=TEXT(A1,"???-??-????")

Vaya con Dios,
Chuck, CABGx3



"Mickie" wrote:

Hi!
I have a column of numbers (2100 to be exact) that I need to insert dashes
so they resemble 333-33-3333. The numbers currently show 333333333. Is
there anything I can do?
Thanks so much for any suggestions..


Mickie

Is there a way to insert dashes in a column of numbers
 
Thanks so much Chuck!

"CLR" wrote:

Another alternative would be.........

=TEXT(A1,"???-??-????")

Vaya con Dios,
Chuck, CABGx3



"Mickie" wrote:

Hi!
I have a column of numbers (2100 to be exact) that I need to insert dashes
so they resemble 333-33-3333. The numbers currently show 333333333. Is
there anything I can do?
Thanks so much for any suggestions..



All times are GMT +1. The time now is 11:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com