Removing zeroes in number format

[DebbyH]

Our customer numbers begin with A followed by four zeroes and a string of numbers.

Example: A00002121256438
A00002178164211
A00002191112936
A00002152291978


How can I create a formula to remove the 4 zeroes following the letter A so that the output is as follows:

A2121256438
A2178164211
A2191112936
A2152291978

Any assistance would be appreciated.

 

[Joe4]

Use the SUBSTITUTE function:

=SUBSTITUTE(A1,"0000","")

 

[Rick Rothstein]

Give this a try...

=REPLACE(A1,2,4,"")

 

[Amanda Wilso]

=substitute(a1,"0000","")

Imgur: The most awesome images on the Internet

 

[Joe4]

=substitute(a1,"0000","")

That looks familiar!

 

[Rick Rothstein]

Use the SUBSTITUTE function:

=SUBSTITUTE(A1,"0000","")

=substitute(a1,"0000","")

What about if the value was this... A00002121200008 ?

Maybe this if you wanted to use SUBSTITUTE...

=SUBSTITUTE(A1,"0000","",1)

although I liked my REPLACE function suggest better.

 

[Tetra201]

Another option:

="A"&--SUBSTITUTE(A1,"A","")

 

[Rick Rothstein]

Another option:

="A"&--SUBSTITUTE(A1,"A","")

What if (at some future date) there are 5 or 6 leading zeroes (after the "A")... the OP indicated he only wanted the first four zeroes removed (because they are always going to be zero).

 

[Tetra201]

Don't see why they would want to convert A000000xxxx into A00xxxx. But this is pure speculation.

On the other hand, if they start having 3 leading zeros in the same 15-position string, my solution would still work. But again, this is pure speculation.

 

[Rick Rothstein]

Don't see why they would want to convert A000000xxxx into A00xxxx. But this is pure speculation.

On the other hand, if they start having 3 leading zeros in the same 15-position string, my solution would still work. But again, this is pure speculation.

The OP specifically said... "Our customer numbers begin with A followed by four zeroes and a string of numbers."... the A and 4 zeroes are always there and, for whatever reason, the OP wants to keep the "A", remove those guaranteed 4 zeroes and leave the "string of numbers" that remain. Personally, I do not see and wiggle room in interpreting that.