Delete a period in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 insert>column)

=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
 
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
 
Back
Top