Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and Replace
Good day all,
I am trying to change this number 01-465-9273 to show up as 014659273. It is easy enough to make the (-) go away but it takes the zeros at the beginning of the number away as well which defeats the purpose Thanks -- Pierre |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and Replace
A couple of alternatives:
- format the cell as Text before removing the dashes - put an apostrophe before the 0 before removing the dashes - remove the dashes in another cell, using SUBSTITUTE: =SUBSTITUTE(A1,"-","") - give the cell a custom number format like 000000000 Hope this helps, Hutch "Pierre" wrote: Good day all, I am trying to change this number 01-465-9273 to show up as 014659273. It is easy enough to make the (-) go away but it takes the zeros at the beginning of the number away as well which defeats the purpose Thanks -- Pierre |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and Replace
Hi,
The first one will not work -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Tom Hutchins" wrote in message ... A couple of alternatives: - format the cell as Text before removing the dashes - put an apostrophe before the 0 before removing the dashes - remove the dashes in another cell, using SUBSTITUTE: =SUBSTITUTE(A1,"-","") - give the cell a custom number format like 000000000 Hope this helps, Hutch "Pierre" wrote: Good day all, I am trying to change this number 01-465-9273 to show up as 014659273. It is easy enough to make the (-) go away but it takes the zeros at the beginning of the number away as well which defeats the purpose Thanks -- Pierre |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and Replace
Thanks Hutch.
First one did not work Second one, if I understood you correctly would require me to do this humpteen times Third, which seemed complicated to me, therefore I tried it last, turned out to be really easy and worked perfectly Forth and last one, worked for that cell, however when I tried to use that cell in another formula, it dropped the zero(s) Thank you very much -- Pierre "Tom Hutchins" wrote: A couple of alternatives: - format the cell as Text before removing the dashes - put an apostrophe before the 0 before removing the dashes - remove the dashes in another cell, using SUBSTITUTE: =SUBSTITUTE(A1,"-","") - give the cell a custom number format like 000000000 Hope this helps, Hutch "Pierre" wrote: Good day all, I am trying to change this number 01-465-9273 to show up as 014659273. It is easy enough to make the (-) go away but it takes the zeros at the beginning of the number away as well which defeats the purpose Thanks -- Pierre |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and Replace
It worked when I tried it before posting the reply, and it works for me now
(Excel 2007). In a new workbook, in cells with a default format of General, I enter the OP's sample data in two cells. If I just remove the dashes from one, it becomes a number and drops the leading zero. If I format the second cell as Text, then remove the dashes, it remains as text with the leading zero intact. I get the same results with other similar leading-zero strings of digits & dashes. 2007 vs. earlier version, maybe? Thanks, Hutch "Ashish Mathur" wrote: Hi, The first one will not work -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Tom Hutchins" wrote in message ... A couple of alternatives: - format the cell as Text before removing the dashes - put an apostrophe before the 0 before removing the dashes - remove the dashes in another cell, using SUBSTITUTE: =SUBSTITUTE(A1,"-","") - give the cell a custom number format like 000000000 Hope this helps, Hutch "Pierre" wrote: Good day all, I am trying to change this number 01-465-9273 to show up as 014659273. It is easy enough to make the (-) go away but it takes the zeros at the beginning of the number away as well which defeats the purpose Thanks -- Pierre |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace: find part cell, replace whole cell | Excel Worksheet Functions | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
find replace cursor default to find box | Excel Discussion (Misc queries) |