|
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! |
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! |
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! |
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! |
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! |
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! |
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! |
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! |
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! |
| All times are GMT +1. The time now is 07:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com