| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
replace - by blank, keep 0 in phonenumber
Hi! I need to format a series of swedish phonenumbers from, for example
045-52698 to 04552698. When I try to use the search and replace, I lose the 0 in the beginning of the number. I also tried to format the cells as text first, but it doesn't help. Can anyone help? Thanx! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
replace - by blank, keep 0 in phonenumber
Use a Helper column filled with this formula:
=TEXT(SUBSTITUTE(A1,"-",""),"00000000") format the original column as text and paste special/value the helper column back to the original column! Regards, Stefi €žsofie€ť ezt Ă*rta: Hi! I need to format a series of swedish phonenumbers from, for example 045-52698 to 04552698. When I try to use the search and replace, I lose the 0 in the beginning of the number. I also tried to format the cells as text first, but it doesn't help. Can anyone help? Thanx! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
replace - by blank, keep 0 in phonenumber
Thank you stefi, I will try that
"Stefi" skrev: Use a Helper column filled with this formula: =TEXT(SUBSTITUTE(A1,"-",""),"00000000") format the original column as text and paste special/value the helper column back to the original column! Regards, Stefi €žsofie€ť ezt Ă*rta: Hi! I need to format a series of swedish phonenumbers from, for example 045-52698 to 04552698. When I try to use the search and replace, I lose the 0 in the beginning of the number. I also tried to format the cells as text first, but it doesn't help. Can anyone help? Thanx! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
replace - by blank, keep 0 in phonenumber
I just tried it, it doesn't seem to work. It doesn't "behave" as a formula.
When I copied and pasted your formula nothing happend... "Stefi" skrev: Use a Helper column filled with this formula: =TEXT(SUBSTITUTE(A1,"-",""),"00000000") format the original column as text and paste special/value the helper column back to the original column! Regards, Stefi €žsofie€ť ezt Ă*rta: Hi! I need to format a series of swedish phonenumbers from, for example 045-52698 to 04552698. When I try to use the search and replace, I lose the 0 in the beginning of the number. I also tried to format the cells as text first, but it doesn't help. Can anyone help? Thanx! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
replace - by blank, keep 0 in phonenumber
Hi Sofie
i have just seen that Stefi posted the same solution as I did. It sounds as though the cell where you are pasting the formula is formatted as Text (from your previous efforts). Format the cell as General, then paste the formula and all should work OK. -- Regards Roger Govier "sofie" wrote in message ... I just tried it, it doesn't seem to work. It doesn't "behave" as a formula. When I copied and pasted your formula nothing happend... "Stefi" skrev: Use a Helper column filled with this formula: =TEXT(SUBSTITUTE(A1,"-",""),"00000000") format the original column as text and paste special/value the helper column back to the original column! Regards, Stefi "sofie" ezt írta: Hi! I need to format a series of swedish phonenumbers from, for example 045-52698 to 04552698. When I try to use the search and replace, I lose the 0 in the beginning of the number. I also tried to format the cells as text first, but it doesn't help. Can anyone help? Thanx! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
replace - by blank, keep 0 in phonenumber
Nope, still doesn't work. The column A with the numbers is formatted as text
and the help column with the fomula as general. Is that correct? Thank you for helping out! "Roger Govier" skrev: Hi Sofie i have just seen that Stefi posted the same solution as I did. It sounds as though the cell where you are pasting the formula is formatted as Text (from your previous efforts). Format the cell as General, then paste the formula and all should work OK. -- Regards Roger Govier "sofie" wrote in message ... I just tried it, it doesn't seem to work. It doesn't "behave" as a formula. When I copied and pasted your formula nothing happend... "Stefi" skrev: Use a Helper column filled with this formula: =TEXT(SUBSTITUTE(A1,"-",""),"00000000") format the original column as text and paste special/value the helper column back to the original column! Regards, Stefi "sofie" ezt Ă*rta: Hi! I need to format a series of swedish phonenumbers from, for example 045-52698 to 04552698. When I try to use the search and replace, I lose the 0 in the beginning of the number. I also tried to format the cells as text first, but it doesn't help. Can anyone help? Thanx! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
replace - by blank, keep 0 in phonenumber
Hey guys!
I found a solution so now i works fine! Thanx for all the help Stefi and Roger! Sofie "sofie" skrev: Nope, still doesn't work. The column A with the numbers is formatted as text and the help column with the fomula as general. Is that correct? Thank you for helping out! "Roger Govier" skrev: Hi Sofie i have just seen that Stefi posted the same solution as I did. It sounds as though the cell where you are pasting the formula is formatted as Text (from your previous efforts). Format the cell as General, then paste the formula and all should work OK. -- Regards Roger Govier "sofie" wrote in message ... I just tried it, it doesn't seem to work. It doesn't "behave" as a formula. When I copied and pasted your formula nothing happend... "Stefi" skrev: Use a Helper column filled with this formula: =TEXT(SUBSTITUTE(A1,"-",""),"00000000") format the original column as text and paste special/value the helper column back to the original column! Regards, Stefi "sofie" ezt Ă*rta: Hi! I need to format a series of swedish phonenumbers from, for example 045-52698 to 04552698. When I try to use the search and replace, I lose the 0 in the beginning of the number. I also tried to format the cells as text first, but it doesn't help. Can anyone help? Thanx! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
replace - by blank, keep 0 in phonenumber
I'm glad to hear you found a solution, but I'm eager to know what is that! I
suppose, Roger is interested in it, too! Regards, Stefi €žsofie€ť ezt Ă*rta: Hey guys! I found a solution so now i works fine! Thanx for all the help Stefi and Roger! Sofie "sofie" skrev: Nope, still doesn't work. The column A with the numbers is formatted as text and the help column with the fomula as general. Is that correct? Thank you for helping out! "Roger Govier" skrev: Hi Sofie i have just seen that Stefi posted the same solution as I did. It sounds as though the cell where you are pasting the formula is formatted as Text (from your previous efforts). Format the cell as General, then paste the formula and all should work OK. -- Regards Roger Govier "sofie" wrote in message ... I just tried it, it doesn't seem to work. It doesn't "behave" as a formula. When I copied and pasted your formula nothing happend... "Stefi" skrev: Use a Helper column filled with this formula: =TEXT(SUBSTITUTE(A1,"-",""),"00000000") format the original column as text and paste special/value the helper column back to the original column! Regards, Stefi "sofie" ezt Ă*rta: Hi! I need to format a series of swedish phonenumbers from, for example 045-52698 to 04552698. When I try to use the search and replace, I lose the 0 in the beginning of the number. I also tried to format the cells as text first, but it doesn't help. Can anyone help? Thanx! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
replace - by blank, keep 0 in phonenumber
Format the Numbers as Custom 00000000
Replace the - with nothing Steve On Tue, 10 Oct 2006 10:23:02 +0100, sofie wrote: Hi! I need to format a series of swedish phonenumbers from, for example 045-52698 to 04552698. When I try to use the search and replace, I lose the 0 in the beginning of the number. I also tried to format the cells as text first, but it doesn't help. Can anyone help? Thanx! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I replace a "#N/A" formula result with a blank in excel? | Excel Discussion (Misc queries) | |||
| Imported Data creates blank cells that aren't really blank | Excel Worksheet Functions | |||
| Setting Purely BLANK Cell | Excel Worksheet Functions | |||
| CONCATENATE problem with blank cells | Excel Discussion (Misc queries) | |||
| Blank Cells in Pivot Tables | Excel Discussion (Misc queries) | |||
Linear Mode
