Use of "Custom" number

[Julia Easter]

Hi I have a column in Excel that needs to be converted as
follows, I think I can do this using "Format" - "Cells" -
"Custom Number" but am not sure what to type in so it
keeps the first character as text followed by 5 digits
(adding a zero to the end)

Account number:-

A4578
B6789
C6892
E8900

The account numbers need to become 6 digit (zero at the
end) but retain the starting letter

HELP!
Ta
Julia

 

[Bernie Deitrick]

Julia,

You can use a formula. If your Account numbers start in A1, then in B1, use
the formula

=LEFT(A1 & "00000",6)

Copy down to match, then copy and paste special values, and get rid of your
original data.

HTH,
Bernie
MS Excel MVP

 

[Frank Kabel]

Hi
if you have different characters at the beginning this is NOT possible
with a custom format. This would require VBA (an event procedure)

 

[Julia Easter]

Hi - that works but is there anyway I can then take the
new number and copy it somewhere else? I cant think of a
way round this! It copies the formula....

Scratching head again!!

 

[Bernie Deitrick]

Julia,

When you paste, right click on the paste area, then use Patespecial Values.

HTH,
Bernie
MS Excel MVP