# Format To Show Specified Numbers

• All,

I have a number format that I need assistance with:

The number format I need is 1234-000 or 1234-001

The normal number format is 1234-000, very rarely does the 1234-001 be used. So I would like the number format to left fill the left section 1234 and right fill the right section 001.
Is this possible?

• Re: Left And/or Right Fill Number Formating

Maybe format as ####-###

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Left And/or Right Fill Number Formating

That's the format I currently have. But if only 4 digits are used, it shows like 0001-234 instead of 1234-000. The reason I need this is, very rarely is the last 3 digits used most entries are only 4 numbers. And to alleviate any typo's where the user is used to only using 4 digits this could confuse a lot of people or mess up our data by adding 3 extra entries. As I stated the format I am looking for is to right fill the right portion of the number series and left fill the left portion of the number series.

• Re: Left And/or Right Fill Number Formating

I can't get what you really want, but based on;

Quote

So I would like the number format to left fill the left section 1234 and right fill the right section 001.

1234-000

• Re: Format To Show Specified Numbers

Not sure if what you are asking is possible, but here are 2 options I came up with - kind of an ugly way to get there, but it seems to do what you are asking

opt 1
Try formatting the cells ####-"000"
Excel treats the "000" as text appended to the number
Only catch is you have to type the hyphen if the entry is all 7 digits

opt 2
Assuming data is in column A
Format column B to ####-###
In column B, set formula as =IF((LEN(A1)>4),A1,A1*1000)

Hope this helps

-Brandon

• Re: Format To Show Specified Numbers

Try this custom format: [COLOR="Blue"][>9999]0000-"00"0;[>=0]0000-"000";General[/COLOR]

So,

nnnn shows as nnnn-000
nnnnm shows as nnnn-00m

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!