replace - by blank, keep 0 in phonenumber

S

sofie

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!
 
S

Stefi

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:
 
S

sofie

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:
Click to expand...
 
S

sofie

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:
 
R

Roger Govier

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.
 
S

sofie

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:
 
S

sofie

Hey guys!

I found a solution so now i works fine! Thanx for all the help Stefi and
Roger!

Sofie



"sofie" skrev:
 
S

Stefi

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:
 
Top