I have a column of data in Excel;
07902555018
07902556019
07902557020
07902558021
07902559022
...
The values change but the placement of three zeros is constant. I am trying to remove the First Zero, Second Zero and the Zero in the 3rd to last position.
792555518
792555619
792555720
...
After the values are changed I am trying to add two hyphens
79-25555-18
79-25556-19
19-25557-20
I tried using: =TEXT(--SUBSTITUTE(--E2,"0","",2),"00-00000-00") However a problem occurs:
079020638020 becomes 792-06380-20 However 79-20638-20 is the value I want returned in instances like these.
If you only know that the placement of the zeros is constant then use REPLACE rather than SUBSTITUTE. This replaces (removes) the 1st, 4th and 9th zeros:
=TEXT(REPLACE(REPLACE(REPLACE(A1,1,1,""),3,1,""),8,1,""),"00-00000-00")
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments