Simple cell formating question

[Metafreak]

Hi guys,
Firstly I apologise if something like this has already been covered but I
have been unable to find anything related.

I'm continuously adding to an ongoing spreadsheet with a range of data at
work and have been trying to make the process a little easier/quicker. One
of the data values is an ID number in the following format "AHPL-000#-####"
where the AHPL and the 0's are constant and the #'s are variable numbers.
Would anyone know how to make excell auto format cells like this? For
example, would it be possible to simply type five numbers (corresponding to
the #'s) and have it come out looking like the above?

I've tried modifying an existing format under 'Custom' in the 'Format Cells'
window and entered 'AHPL-000#-####' but an error occurs saying that Office
Excel cannot use the number format you typed.

Any help would be greatly appreciated.

 

[Dave Peterson]

Try:

\A\HPL-\0\0\00-0000
or
"AHPL-000"0-0000

Hi guys,
Firstly I apologise if something like this has already been covered but I
have been unable to find anything related.

I'm continuously adding to an ongoing spreadsheet with a range of data at
work and have been trying to make the process a little easier/quicker. One
of the data values is an ID number in the following format "AHPL-000#-####"
where the AHPL and the 0's are constant and the #'s are variable numbers.
Would anyone know how to make excell auto format cells like this? For
example, would it be possible to simply type five numbers (corresponding to
the #'s) and have it come out looking like the above?

I've tried modifying an existing format under 'Custom' in the 'Format Cells'
window and entered 'AHPL-000#-####' but an error occurs saying that Office
Excel cannot use the number format you typed.

Any help would be greatly appreciated.

 

[Metafreak]

Wow, thanks for such a prompt reply. Unfortunately this doesn't seem to
work. I tried both examples and the result is a line of ######### in the
cell.
Just to reiterate, would it be possible to program excel in such a way that
if I were to type for example 47213 or 4-7213, excel would automatically
format it to AHPL-0004-7213?

 

[Fred Smith]

Dave answered your question; you don't need to reiterate it. #s mean that
your cell is not wide enough to display the result. Widen the column -- that
should solve your problem.

Regards,
Fred.

 

[Metafreak]

Ahh, great. It indeed worked like a charm.

Thanks for the help guys.