Custom Number Format

[HospitalOfficer]

Please help, what is the best way to convert the following D00001234567 to the following D1234567?

Thank you.

[Pete_UK]

Assuming that is in A1, you can do this:

=SUBSTITUTE(A1,"D0000","D")

Or you could use Find & Replace (Ctrl-H), to find D0000 and replace it with D.

Hope this helps.

Pete

[HospitalOfficer]

Thank you Pete. It worked perfectly.

[newdoverman]

Depending upon the real situation, these may work for you.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Pete_UK]

Hi Ron,

the second of your formulae would fail if you had something like D00001000023.

Pete

[newdoverman]

Hi Pete, that is why I said "may depending upon...". You are correct of course with the example you give with the double occurrence of the 0s

If there was a possibility of the double occurrence of 0000 then this would work.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

will still work.