Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
Mickie
 
Posts: n/a
Default 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..
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.newusers
Ron Coderre
 
Posts: n/a
Default 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..

  #4   Report Post  
Posted to microsoft.public.excel.newusers
Pete_UK
 
Posts: n/a
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.newusers
Mickie
 
Posts: n/a
Default 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..



  #6   Report Post  
Posted to microsoft.public.excel.newusers
Pete_UK
 
Posts: n/a
Default Is there a way to insert dashes in a column of numbers

Thanks for feeding back.

Pete

  #7   Report Post  
Posted to microsoft.public.excel.newusers
CLR
 
Posts: n/a
Default 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..

  #8   Report Post  
Posted to microsoft.public.excel.newusers
Mickie
 
Posts: n/a
Default 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..

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 to insert text in one column and populate numbers in another Alice Excel Worksheet Functions 0 March 28th 06 07:27 PM
Creating a column of numbers from a column with some blanks? Knut Excel Discussion (Misc queries) 0 March 20th 06 01:36 AM
Return SEARCHED Column Number of Numeric Label and Value Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 07:16 PM
I cannot get a column of numbers to sum, why? HHG Excel Discussion (Misc queries) 8 February 8th 05 01:55 PM
I cannot get a column of numbers to sum, why? HHG Excel Worksheet Functions 4 February 8th 05 05:23 AM


All times are GMT +1. The time now is 12:21 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"