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!