Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing dashes from numbers
Look at EDIT=Replace.
"Pat Jones" wrote: Hi; I have a long column of numbers that contain "-" (I call them dashes). I need to remove the dashes from these 3000+ numbers. Is there a way to automate this process? Thanks; Pat |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing dashes from numbers
Highlight the column and do Find & Replace (CTRL-H):
Find What: - Replace with: (leave empty) Click Replace All. Note that if you have "numbers" like 0012-3456, you will lose the leading zeros and end up with 123456. Hope this helps. Pete On Jun 21, 4:39 pm, "Pat Jones" wrote: Hi; I have a long column of numbers that contain "-" (I call them dashes). I need to remove the dashes from these 3000+ numbers. Is there a way to automate this process? Thanks; Pat |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing dashes from numbers
is the dash really there or is the dash added though formatting?
If they act like numbers when you add them, probably <format<cell<number<general will get rid of them "Pat Jones" wrote: Hi; I have a long column of numbers that contain "-" (I call them dashes). I need to remove the dashes from these 3000+ numbers. Is there a way to automate this process? Thanks; Pat |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing dashes from numbers
Hi;
I have a long column of numbers that contain "-" (I call them dashes). I need to remove the dashes from these 3000+ numbers. Is there a way to automate this process? Thanks; Pat |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing dashes from numbers
The following formula will return text and thus preserve the 00's.
=SUBSTITUTE(A2,"-","") HTH Kostis Vezerides On Jun 21, 8:31 pm, "Pat Jones" wrote: Thanks Pete; That works except for one thing that I didn't realize was important - many of these numbers start with a zero (0). The find and replace got rid of the zeros. I then formatted the cells as text, but the zeros were still gone on some - but not all - of the results. Any ideas how to preserve those zeros ? Cheers; Pat "Pete_UK" wrote in message ps.com... Highlight the column and do Find & Replace (CTRL-H): Find What: - Replace with: (leave empty) Click Replace All. Note that if you have "numbers" like 0012-3456, you will lose the leading zeros and end up with 123456. Hope this helps. Pete On Jun 21, 4:39 pm, "Pat Jones" wrote: Hi; I have a long column of numbers that contain "-" (I call them dashes). I need to remove the dashes from these 3000+ numbers. Is there a way to automate this process? Thanks; Pat |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing dashes from numbers
Use an empty column. If your first number is in A1, use this formula
in the first row of your helper column: =LEFT(A1,FIND("-",A1)-1)&RIGHT(A1,LEN(A1)-FIND("-",A1)) This will force the cell containing this formula to Text format. Copy the formula down as far as you need, then copy all the new cells, and without changing your selection, click Edit|Paste Special (Values). Then you can copy the resulting values over your old column. Mark Lincoln On Jun 21, 1:31 pm, "Pat Jones" wrote: Thanks Pete; That works except for one thing that I didn't realize was important - many of these numbers start with a zero (0). The find and replace got rid of the zeros. I then formatted the cells as text, but the zeros were still gone on some - but not all - of the results. Any ideas how to preserve those zeros ? Cheers; Pat "Pete_UK" wrote in message ps.com... Highlight the column and do Find & Replace (CTRL-H): Find What: - Replace with: (leave empty) Click Replace All. Note that if you have "numbers" like 0012-3456, you will lose the leading zeros and end up with 123456. Hope this helps. Pete On Jun 21, 4:39 pm, "Pat Jones" wrote: Hi; I have a long column of numbers that contain "-" (I call them dashes). I need to remove the dashes from these 3000+ numbers. Is there a way to automate this process? Thanks; Pat- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing dashes from numbers
Much simpler and more elegant than my example. I'll have to remember
this. Mark Lincoln On Jun 21, 1:12 pm, vezerid wrote: The following formula will return text and thus preserve the 00's. =SUBSTITUTE(A2,"-","") HTH Kostis Vezerides On Jun 21, 8:31 pm, "Pat Jones" wrote: Thanks Pete; That works except for one thing that I didn't realize was important - many of these numbers start with a zero (0). The find and replace got rid of the zeros. I then formatted the cells as text, but the zeros were still gone on some - but not all - of the results. Any ideas how to preserve those zeros ? Cheers; Pat "Pete_UK" wrote in message ups.com... Highlight the column and do Find & Replace (CTRL-H): Find What: - Replace with: (leave empty) Click Replace All. Note that if you have "numbers" like 0012-3456, you will lose the leading zeros and end up with 123456. Hope this helps. Pete On Jun 21, 4:39 pm, "Pat Jones" wrote: Hi; I have a long column of numbers that contain "-" (I call them dashes). I need to remove the dashes from these 3000+ numbers. Is there a way to automate this process? Thanks; Pat- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing dashes from numbers
Thanks Pete;
That works except for one thing that I didn't realize was important - many of these numbers start with a zero (0). The find and replace got rid of the zeros. I then formatted the cells as text, but the zeros were still gone on some - but not all - of the results. Any ideas how to preserve those zeros ? Cheers; Pat "Pete_UK" wrote in message ps.com... Highlight the column and do Find & Replace (CTRL-H): Find What: - Replace with: (leave empty) Click Replace All. Note that if you have "numbers" like 0012-3456, you will lose the leading zeros and end up with 123456. Hope this helps. Pete On Jun 21, 4:39 pm, "Pat Jones" wrote: Hi; I have a long column of numbers that contain "-" (I call them dashes). I need to remove the dashes from these 3000+ numbers. Is there a way to automate this process? Thanks; Pat |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing dashes from numbers
That's beautiful.
Thanks Kostis Pat "vezerid" wrote in message ups.com... The following formula will return text and thus preserve the 00's. =SUBSTITUTE(A2,"-","") HTH Kostis Vezerides On Jun 21, 8:31 pm, "Pat Jones" wrote: Thanks Pete; That works except for one thing that I didn't realize was important - many of these numbers start with a zero (0). The find and replace got rid of the zeros. I then formatted the cells as text, but the zeros were still gone on some - but not all - of the results. Any ideas how to preserve those zeros ? Cheers; Pat "Pete_UK" wrote in message ps.com... Highlight the column and do Find & Replace (CTRL-H): Find What: - Replace with: (leave empty) Click Replace All. Note that if you have "numbers" like 0012-3456, you will lose the leading zeros and end up with 123456. Hope this helps. Pete On Jun 21, 4:39 pm, "Pat Jones" wrote: Hi; I have a long column of numbers that contain "-" (I call them dashes). I need to remove the dashes from these 3000+ numbers. Is there a way to automate this process? Thanks; Pat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing dashes from ISBN's while still keeping 1st number (zero) | Excel Worksheet Functions | |||
removing dashes changes test into number | Excel Discussion (Misc queries) | |||
Convert phone numbers with dashes in them to just numbers | Excel Discussion (Misc queries) | |||
Convert phone numbers with dashes in them to just numbers | Excel Discussion (Misc queries) | |||
removing dashes from phone numbers | Excel Discussion (Misc queries) |