I have below data in the excel from an import.

001-8420-849900

I want to change into 0018420.849900.

I know how to replace "-" with "." but not sure how to delete one of "."

Thanks!

if it's always the same number of digits between the dashes, you can use the
=LEFT and =RIGHT functions to split it up and =CONCATENATE it back together
without the dashes then use the find/replace

"Dar Dar" wrote:

I have below data in the excel from an import.

001-8420-849900

I want to change into 0018420.849900.

I know how to replace "-" with "." but not sure how to delete one of "."

Thanks!

You can use a help column with formulas, assume the import starts in A2, in
B2 put (if B2 is taken select column B and do insertcolumn)

=SUBSTITUTE(SUBSTITUTE(A2,"-",".",2),"-","")

copy down as long as needed and the copy and paste special as values over
the old values


finally delete the help column

of course this assumes that there are 2 dashes/hyphens to replace and that
you want to remove the first and replace the second with a period


--
Regards,

Peo Sjoblom


"Dar Dar" <Dar wrote in message
...
I have below data in the excel from an import.

001-8420-849900

I want to change into 0018420.849900.

I know how to replace "-" with "." but not sure how to delete one of "."

Thanks!

Hi Peo,

Thanks so much. It works like magic. :)

By any chance, do you know if there is a way to set up in Excel to
automatically change to that format all time?

Thanks again. Have a great day.

Dar Dar

"Peo Sjoblom" wrote:

You can use a help column with formulas, assume the import starts in A2, in
B2 put (if B2 is taken select column B and do insertcolumn)

=SUBSTITUTE(SUBSTITUTE(A2,"-",".",2),"-","")

copy down as long as needed and the copy and paste special as values over
the old values


finally delete the help column

of course this assumes that there are 2 dashes/hyphens to replace and that
you want to remove the first and replace the second with a period


--
Regards,

Peo Sjoblom


"Dar Dar" <Dar wrote in message
...
I have below data in the excel from an import.

001-8420-849900

I want to change into 0018420.849900.

I know how to replace "-" with "." but not sure how to delete one of "."

Thanks!

Another way...

=REPLACE(REPLACE(A2,FIND("-",A2,FIND("-",A2)+1),1,"."),FIND("-",A2),1,"")


"Dar Dar" wrote:

I have below data in the excel from an import.

001-8420-849900

I want to change into 0018420.849900.

I know how to replace "-" with "." but not sure how to delete one of "."

Thanks!