1

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.

Improve this question
2
  • Could it be that your cell is formated as number ? Which means the leading 0 get ignored. Format it as text with the leading 0 and it should work Commented Nov 17, 2017 at 13:29
  • I apologize i put an extra zero in the command and gave the wrong value in the Original post. 00-00000-00 is the format desired. I've checked the cell formatting and it is definitely Text, not number Commented Nov 17, 2017 at 13:49

2 Answers 2

Reset to default
1

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")
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

@Callum-Thank you so much that actually solved the issue. I am new to excel and learning the commands. This may be asking a little much but I notice you have three nested Replace commands. What would occur if for instance in this command if a zero was omitted from that placement.
Hi Neil, glad it helped. I don't exactly understand what you mean but the formula blindly replaces the 1/4/9 characters. If it was applied to a different string (e.g. no zero at the beginning) it would still replace those characters, whether they were zeros or not. Does that answer your question?
Yes that does answer the question. That was exactly what I was trying to understand if it was replacing the 1st,4th and 9th character or looking for the actual zero. This still solves my issue because I generally have those zeros in those place always, if not I can add that.
I noticed it's actually the 10th you're excluding (the initial example is a bit misleading). Anyway, you can use this (if you want) to check if you have zeros in the right positions: =(MID(A1,1,1)+MID(A1,4,1)+MID(A1,10,1))=0
Its returns true for each. I suppose how you wrote the intial formula doesnt seem to change the outcome as its still correctly displaying for some 800+ records I ran it against. Works exactly as I needed and accomplishes what took me 3 columns to do using Replace, Concat and Substitute.
0

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

Improve this answer

1 Comment

I apologize i put an extra zero in the command and gave the wrong value in the Original post. 00-00000-00 is the format desired. I've checked the cell formatting and it is definitely Text, not number

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.