Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformat numbers
Hello,
I have a bunch of 9 digit numbers in a column that I'd like reformatted. I'd like to remove the leading zeros and put the remaining numbers in xxxxxx-xx-x format. For instance: 123456789 becomes 123456-78-9 000012345 becomes 12-34-5 Thanks in advance, Ellen |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformat numbers
try:
A1=number in B1: =TEXT(A1,"######-##-#") "EllenM" wrote: Hello, I have a bunch of 9 digit numbers in a column that I'd like reformatted. I'd like to remove the leading zeros and put the remaining numbers in xxxxxx-xx-x format. For instance: 123456789 becomes 123456-78-9 000012345 becomes 12-34-5 Thanks in advance, Ellen |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformat numbers
One way:
Format/Cells/Number/Custom #0-00-0 In article , EllenM wrote: Hello, I have a bunch of 9 digit numbers in a column that I'd like reformatted. I'd like to remove the leading zeros and put the remaining numbers in xxxxxx-xx-x format. For instance: 123456789 becomes 123456-78-9 000012345 becomes 12-34-5 Thanks in advance, Ellen |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformat numbers
I use Custom Format #-##-# to get
123456789 to 123456-78-9 12345 to 12-34-5 But I do not understand you reference to leading zeros perhaps you already have custom format to get them best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "EllenM" wrote in message ... Hello, I have a bunch of 9 digit numbers in a column that I'd like reformatted. I'd like to remove the leading zeros and put the remaining numbers in xxxxxx-xx-x format. For instance: 123456789 becomes 123456-78-9 000012345 becomes 12-34-5 Thanks in advance, Ellen |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformat numbers
do you want just the display to have dashes or what is actually in the cell
for display format-cell-number-custom 0-00-0 this assumes what is in the cell is really a number and not text. "EllenM" wrote: Hello, I have a bunch of 9 digit numbers in a column that I'd like reformatted. I'd like to remove the leading zeros and put the remaining numbers in xxxxxx-xx-x format. For instance: 123456789 becomes 123456-78-9 000012345 becomes 12-34-5 Thanks in advance, Ellen |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformat numbers
Thanks to everyone who answered my question. All your quick responses worked!!
"Bernard Liengme" wrote: I use Custom Format #-##-# to get 123456789 to 123456-78-9 12345 to 12-34-5 But I do not understand you reference to leading zeros perhaps you already have custom format to get them best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "EllenM" wrote in message ... Hello, I have a bunch of 9 digit numbers in a column that I'd like reformatted. I'd like to remove the leading zeros and put the remaining numbers in xxxxxx-xx-x format. For instance: 123456789 becomes 123456-78-9 000012345 becomes 12-34-5 Thanks in advance, Ellen |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformat numbers
Hi Bernard,
As for your mention of the leading zeros, the data is in the format of text, I think. They've got a green triangle in the upper left side of the cell. My first step was to convert the data to number with multiply in the paste special box. Ellen "Bernard Liengme" wrote: I use Custom Format #-##-# to get 123456789 to 123456-78-9 12345 to 12-34-5 But I do not understand you reference to leading zeros perhaps you already have custom format to get them best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "EllenM" wrote in message ... Hello, I have a bunch of 9 digit numbers in a column that I'd like reformatted. I'd like to remove the leading zeros and put the remaining numbers in xxxxxx-xx-x format. For instance: 123456789 becomes 123456-78-9 000012345 becomes 12-34-5 Thanks in advance, Ellen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reformat spreadsheet | Excel Worksheet Functions | |||
Reformat of a table | Excel Discussion (Misc queries) | |||
Reformat column of telephone numbers | Excel Discussion (Misc queries) | |||
Reformat telephone numbers in a column so all are formatted the s. | Excel Discussion (Misc queries) | |||
How to reformat numbers stored as text (apostrophe at beginning) | Excel Discussion (Misc queries) |