CODEWITHSUNDEEP
excelexcel-formulaformatting
Neil
Neil

Reputation: 63

Excel Formatting Numbers in Cell

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.

Upvotes: 1

Views: 185

Answers (2)

CallumDA
CallumDA

Reputation: 12113

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")

Upvotes: 1

Doomenik
Doomenik

Reputation: 866

Probably:

079020638020 (Cell E2) is formatted as number. Which means its actually: 79020638020

Your formula removes the second occurence: 79020638020

So you have 2 possibilities to get it working, either format the cell as text (So the leading 0 will not get deleted) or change the formula to:

=TEXT(--SUBSTITUTE(--E2,"0","", 1),"00-000000-00")

Result would be: 79-206380-20

Upvotes: 0

Related Questions